DateTime types and getdate() comparison

SQL 2000. Let's say column MyDate is a datetime type. Is this
comparison syntax OK as is?
... where MyDate <= getdate()
Or is some formatting of the column value and/or of the function's
return value required for the comparison to work?
Thanks
LiamComparison operators (<,>,=, <>, >=, <= ) are allowed between two values wit
h
a datatype of datetime. Your expression is fine.
However, if you want to do things like add or subtract datetime values, you
will need to use the date and time functions in SQL Server.
"Liam" wrote:

> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> .... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam
>|||depends on what you need
but don't convert the column - you'll lose any sargability if it's indexed.
i tend not to try to rely on date data having being inserted with a time
of midnight, so i convert the variable and perform range queries
if you need mydate <= just the date: then do
MyDate < tomorrow at midnight
e.g.
where MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need MyDate for just today
where MyDate >= dateadd(day, datediff(day, 0, getdate()), 0)
and MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need mydate <= current date and time, then simply using
getdate() is appropriate.
Liam wrote:
> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> ... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam

DateTime comparison with some exceptions

I have StratDateTime and EndDateTime fields in the table. I need to compare this two datetime fields and find seconds. I can use DateDiff but there are the following exceptions:

1. Exclude seconds coming from the date which are Saturday and Sunday

2. Exclude seconds coming from time range between 7:01pm and 6:59am

3. Exclude seconds coming from Jan 1^st and Jul 4^th.

So do you want to make the difference between the two columns in seconds a column in the query results or do you want to compare them to each other or some other values in the WHERE clause of the query? I'd suggest that you post the query you have written and then one of us can help you with the query, as it is you have not really posted enough information for us to help you.|||

Ok. Thank you very much for your response.

SELECT Datediff(ss,StartDateTime,EndDateTime) AS mySeconds

FROM MyTable

This will return seconds. However this does not hold all the exceptions I listed above. Let’s say I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

|||This is the same question asked by you before

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1022431&SiteID=1

I will again suggest you to use the calender-table. This will make life easy as you are not able to know 12/16/2006, is Saturday or working day.
With calender-table you can easily find that, more over you can create the holiday list too, find the difference between any date & more functionality can be add according to your own requirements.

Gurpreet S. Gill|||

Thank you very much for your help. That does not work for me since it is considering the day, not the time. My business day should be between 7:00am and 7:00pm in the weekdays. I do not see how getting number of business days would really help.

I would ask the same question, let’s say I have a calendar table, how would I get calendar table return me 2 hours for the following example. I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

Thanks you very much for your help.

|||

What you need to do is create a user defined function to calculate your desired value. It will look like this, I haven't put in all the conditional code for you, that will take a while, but you get the idea.

CREATE FUNCTION BusinessSeconds(@.StartTime datetime, @.EndTime datetime)
RETURNS int
AS
BEGIN
DECLARE @.retVal int
SET @.retVal = datediff(ss, @.StartTime, @.EndTime)
--Conditional code here to subtract your non-business periods
--eg. IF ... SET @.retVal = @.retVal - 86400
RETURN @.retVal
END

And you'll use it like this

SELECT dbo.BusinessSeconds(StartDateTime, EndDateTime) AS MySeconds
FROM MyTable

Datetime comparison problem

Hi,
I am having trouble finding any records with a datetime of 2006-06-16
09:04:39:347. This time clearly falls between the beginning and end times.
I'm even converting to a char to see if that works. It seems to work if I
use .346 but sometimes it requires a difference of .003. Can anyone see what
I might be doing wrong? Thanks Ellie
declare @.dPrevDate datetime
declare @.dNextDate datetime
select @.dPrevDate = '2006-06-16 09:04:39.346'
select @.dNextDate = '2006-06-16 09:05:56.110'
Select *
where (convert(char, tblChanges.dateaction, 21) > convert(char,
@.dPrevDate, 21) and
convert(char, tblChanges.dateaction, 21) < convert(char, @.dNextDate,
21))Sorry I made typos, I should have said that it DIDN'T work for 346 but did
work for 345 (when 346 is clearly less than 347) and the code should be:
>
>
"Ellie" <nospam@.nospam.net> wrote in message
news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see
> what I might be doing wrong? Thanks Ellie
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char,
> @.dNextDate, 21))
>
>|||Due to a design flaw with Intel based chips, compute clock time is accurate
to the nearest three thousandth of a second.
This makes it difficult to coordinate precise times from a different time so
urce to the times created by a Intel based computer. For example, an automat
ed process control system have be recording times that are .003 seconds 'of
f' what the computer thinks.
You have to build in the .003 'fudge' factor.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ellie" <nospam@.nospam.net> wrote in message news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...[
color=darkred]
> Hi,
>
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see wh
at
> I might be doing wrong? Thanks Ellie
>
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
>
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char, @.dNextDate
,
> 21))
>
>
>
>[/color]|||I was thinking that there must have been a problem there but why can't I
convert it to a string and have it more accurate? That isn't working either.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
Due to a design flaw with Intel based chips, compute clock time is accurate
to the nearest three thousandth of a second.
This makes it difficult to coordinate precise times from a different time
source to the times created by a Intel based computer. For example, an
automated process control system have be recording times that are .003
seconds 'off' what the computer thinks.
You have to build in the .003 'fudge' factor.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ellie" <nospam@.nospam.net> wrote in message
news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see
> what
> I might be doing wrong? Thanks Ellie
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char,
> @.dNextDate,
> 21))
>
>|||I don't have any clue what "isn't working" means. However, I do have a
suggestion for storing accuracies under 3 ms.
When you enter the data to SQL Server, you can store the date +
minutes/seconds in datetime, then store *your* milliseconds in a separate
INT column. Or to save space you could use smalldatetime for date + minutes
and store seconds*1000+milliseconds in a separate INT column.
Now, when you query, you'll have something more complex to work with, and
that will be the harder part, but your data will be there. You can just
query for the times within your minute, and then where the milliseconds are
in the range you're after.
"Ellie" <nospam@.nospam.net> wrote in message
news:uTH03CvlGHA.884@.TK2MSFTNGP05.phx.gbl...
>I was thinking that there must have been a problem there but why can't I
>convert it to a string and have it more accurate? That isn't working
>either.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
> Due to a design flaw with Intel based chips, compute clock time is
> accurate to the nearest three thousandth of a second.
> This makes it difficult to coordinate precise times from a different time
> source to the times created by a Intel based computer. For example, an
> automated process control system have be recording times that are .003
> seconds 'off' what the computer thinks.
> You have to build in the .003 'fudge' factor.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Ellie" <nospam@.nospam.net> wrote in message
> news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
>|||Arnie Rowland (arnie@.1568.com) writes:
> Due to a design flaw with Intel based chips, compute clock time is > accurate to t
he nearest three thousandth of a second.

> This makes it difficult to coordinate precise times from a different
> time source to the times created by a Intel based computer. For example,
> an automated process control system have be recording times that are
> .003 seconds 'off' what the computer thinks.
Eh? It's perfectly possible in Windows to handle time down to
dissolution of 100 ns, I believe. At least, you can get far below
3.33 ms. For instance, in the SQL 2005 Profiler, you can opt to get
durations in s.
In fact, if you look at
http://manuals.sybase.com:80/online...=2862;lang=sv#X
you can see that Sybase has the same accuracy. And, as may know
Microsoft SQL Server was originally derived from Sybase. And Sybase
has its origin on Unix. So I doubt that whereever the design flaw
was, that it was with Intel chips.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You are seeing a limit of the DATETIME datatype in SQL Server. From
the Books on Line: "...to an accuracy of one three-hundredth of a
second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values
are rounded to increments of .000, .003, or .007 seconds..."
So, to store the time more accurately you must, as Aaron explained,
have to "roll your own".
Roy Harvey
Beacon Falls, CT
On Fri, 23 Jun 2006 14:13:22 -0400, "Ellie" <nospam@.nospam.net> wrote:

>I was thinking that there must have been a problem there but why can't I
>convert it to a string and have it more accurate? That isn't working either
.
>
>"Arnie Rowland" <arnie@.1568.com> wrote in message
>news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
>Due to a design flaw with Intel based chips, compute clock time is accurate
>to the nearest three thousandth of a second.
>This makes it difficult to coordinate precise times from a different time
>source to the times created by a Intel based computer. For example, an
>automated process control system have be recording times that are .003
>seconds 'off' what the computer thinks.
>You have to build in the .003 'fudge' factor.|||My brain checked out -it is a code artifact that predates Intel chip
design -is more in keeping of what I wanted to say. The latest IntelAMD
chips are capable of almost s precision.
Erlund, Thanks for catching this.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97EC62C58ABBYazorman@.127.0.0.1...
> Arnie Rowland (arnie@.1568.com) writes:
>
> Eh? It's perfectly possible in Windows to handle time down to
> dissolution of 100 ns, I believe. At least, you can get far below
> 3.33 ms. For instance, in the SQL 2005 Profiler, you can opt to get
> durations in s.
> In fact, if you look at
> http://manuals.sybase.com:80/online...=2862;lang=sv#X
> you can see that Sybase has the same accuracy. And, as may know
> Microsoft SQL Server was originally derived from Sybase. And Sybase
> has its origin on Unix. So I doubt that whereever the design flaw
> was, that it was with Intel chips.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||So does this mean that internally they are not rounded (0.00333) but what
I'm seeing in query analyzer when I try to query it, is the rounded .000,
.003 or .007, etc.? I'm not concerned about the accurate storing of the tim
e
but the query afterwards. If they are stored inaccurately, I should still be
able to use that query, unless it is stored internally pre-rounding. I still
don't get why I can't convert it to a string and do a string comparison
though. Hope I am making sense.
Thanks for all of your help. I'll read some of the links and work around
this.
Ellie
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:u21p9299dg97vdtin85imfs525fkefinc7@.
4ax.com...
> You are seeing a limit of the DATETIME datatype in SQL Server. From
> the Books on Line: "...to an accuracy of one three-hundredth of a
> second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values
> are rounded to increments of .000, .003, or .007 seconds..."
> So, to store the time more accurately you must, as Aaron explained,
> have to "roll your own".
> Roy Harvey
> Beacon Falls, CT
> On Fri, 23 Jun 2006 14:13:22 -0400, "Ellie" <nospam@.nospam.net> wrote:
>|||Effectively they are rounded (as demonstrated below) and stored that
way. Comparison of a string against a datetime is performed by
comparing datetime types after converting the string. The string
conversion follows the rules demonstrated below.
Lets look at them as strings, and as strings converted to datetime:
select '2006-06-16 09:04:39.338',
convert(datetime,'2006-06-16 09:04:39.338') UNION
select '2006-06-16 09:04:39.339',
convert(datetime,'2006-06-16 09:04:39.339') UNION
select '2006-06-16 09:04:39.340',
convert(datetime,'2006-06-16 09:04:39.340') UNION
select '2006-06-16 09:04:39.341',
convert(datetime,'2006-06-16 09:04:39.341') UNION
select '2006-06-16 09:04:39.342',
convert(datetime,'2006-06-16 09:04:39.342') UNION
select '2006-06-16 09:04:39.343',
convert(datetime,'2006-06-16 09:04:39.343') UNION
select '2006-06-16 09:04:39.344',
convert(datetime,'2006-06-16 09:04:39.344') UNION
select '2006-06-16 09:04:39.345',
convert(datetime,'2006-06-16 09:04:39.345') UNION
select '2006-06-16 09:04:39.346',
convert(datetime,'2006-06-16 09:04:39.346') UNION
select '2006-06-16 09:04:39.347',
convert(datetime,'2006-06-16 09:04:39.347') UNION
select '2006-06-16 09:04:39.348',
convert(datetime,'2006-06-16 09:04:39.348') UNION
select '2006-06-16 09:04:39.349',
convert(datetime,'2006-06-16 09:04:39.349') UNION
select '2006-06-16 09:04:39.350',
convert(datetime,'2006-06-16 09:04:39.350') UNION
select '2006-06-16 09:04:39.351',
convert(datetime,'2006-06-16 09:04:39.351') UNION
select '2006-06-16 09:04:39.352',
convert(datetime,'2006-06-16 09:04:39.352')
order by 1
2006-06-16 09:04:39.338 2006-06-16 09:04:39.337
2006-06-16 09:04:39.339 2006-06-16 09:04:39.340
2006-06-16 09:04:39.340 2006-06-16 09:04:39.340
2006-06-16 09:04:39.341 2006-06-16 09:04:39.340
2006-06-16 09:04:39.342 2006-06-16 09:04:39.343
2006-06-16 09:04:39.343 2006-06-16 09:04:39.343
2006-06-16 09:04:39.344 2006-06-16 09:04:39.343
2006-06-16 09:04:39.345 2006-06-16 09:04:39.347
2006-06-16 09:04:39.346 2006-06-16 09:04:39.347
2006-06-16 09:04:39.347 2006-06-16 09:04:39.347
2006-06-16 09:04:39.348 2006-06-16 09:04:39.347
2006-06-16 09:04:39.349 2006-06-16 09:04:39.350
2006-06-16 09:04:39.350 2006-06-16 09:04:39.350
2006-06-16 09:04:39.351 2006-06-16 09:04:39.350
2006-06-16 09:04:39.352 2006-06-16 09:04:39.353
I hope that helps.
Roy Harvey
Beacon Falls, CT
On Sat, 24 Jun 2006 07:48:43 -0400, "Ellie" <nospam@.nospam.net> wrote:

>So does this mean that internally they are not rounded (0.00333) but what
>I'm seeing in query analyzer when I try to query it, is the rounded .000,
>.003 or .007, etc.? I'm not concerned about the accurate storing of the tim
e
>but the query afterwards. If they are stored inaccurately, I should still b
e
>able to use that query, unless it is stored internally pre-rounding. I stil
l
>don't get why I can't convert it to a string and do a string comparison
>though. Hope I am making sense.
>Thanks for all of your help. I'll read some of the links and work around
>this.
>Ellie
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:u21p9299dg97vdtin85imfs525fkefinc7@.
4ax.com...
>

datediff comparison to previous row in select statement

In a table I have information of a vehicle's status with an appropriate
date, what I am trying to achieve is to display the number of days
between two dates of two different statuses. Problem is that the same
scenario can happen on more than one occasion and there is no data
linking 2 statuses together. The query ran is
select distinct v.[reg no_],vsh.status,convert(char(16),vsh.[from
datetime],20)
from vehicle v
left join [vehicle status history] vsh on v.[vehicle serial no_] =
vsh.[vehicle serial no_]
and vsh.status IN('BOOKING-IN','DESPATCHED')
where v.[reg no_]='R3RTF'
order by 3
reg no_ status
-- -- --
R3RTF BOOKING-IN 2005-01-11 08:22
R3RTF DESPATCHED 2005-02-03 18:34
R3RTF BOOKING-IN 2005-02-04 09:58
R3RTF DESPATCHED 2005-02-04 14:52
R3RTF BOOKING-IN 2005-04-05 14:33
R3RTF DESPATCHED 2005-06-01 17:37
Looking at these results what I need is the date difference between the
BOOKING-IN and DESPATCHED dates. From the result set above the 3
datediff (for days) values would be 23, 0 & 57.
This is all part of a bigger result set and ideally I would like the
sum of the 3 date differences shown in this example. (23 + 0 + 57 = 80)
I am struggling to get this in a select statement, I have sampled using
cursors but I'm not sure this is the way to go. Using a SELECT
statement would be ideal.
Any help would be much appreciated.You can get the time a vehicle was booked in at a certain occasion with:
SELECT vd.[vehicle serial no_], vd.[from datetime], MIN(DATEDIFF(dd,
vb.[from datetime], vd.[from datetime])
FROM [vehicle status history] vd
INNER JOIN [vehicle status history] vb
ON vd.[vehicle serial no_] = vb.[vehicle serial no_]
AND vd.[from datetime] > vb.[from datetime]
WHERE vd.status = 'DESPATCHED'
AND vd.status = 'BOOKING-IN'
GROUP BY vd.[vehicle serial no_], vd.[from datetime]
If you want to have the sum of the times a vehicle spend booked in, you can
SUM over the previous query:
SELECT [vehicle serial no_], SUM(days_spend)
FROM (
SELECT vd.[vehicle serial no_], MIN(DATEDIFF(dd, vb.[from datetime],
vd.[from datetime]) AS days_spend
FROM [vehicle status history] vd
INNER JOIN [vehicle status history] vb
ON vd.[vehicle serial no_] = vb.[vehicle serial no_]
AND vd.[from datetime] > vb.[from datetime]
WHERE vd.status = 'DESPATCHED'
AND vd.status = 'BOOKING-IN'
GROUP BY vd.[vehicle serial no_], vd.[from datetime]
) ds
GROUP BY [vehicle serial no_]
(everything untested)
Jacco Schalkwijk
SQL Server MVP
"robz8701" <robz8701@.hotmail.com> wrote in message
news:1128070087.999572.326490@.g49g2000cwa.googlegroups.com...
> In a table I have information of a vehicle's status with an appropriate
> date, what I am trying to achieve is to display the number of days
> between two dates of two different statuses. Problem is that the same
> scenario can happen on more than one occasion and there is no data
> linking 2 statuses together. The query ran is
> select distinct v.[reg no_],vsh.status,convert(char(16),vsh.[from
> datetime],20)
> from vehicle v
> left join [vehicle status history] vsh on v.[vehicle serial no_] =
> vsh.[vehicle serial no_]
> and vsh.status IN('BOOKING-IN','DESPATCHED')
> where v.[reg no_]='R3RTF'
> order by 3
> reg no_ status
> -- -- --
> R3RTF BOOKING-IN 2005-01-11 08:22
> R3RTF DESPATCHED 2005-02-03 18:34
> R3RTF BOOKING-IN 2005-02-04 09:58
> R3RTF DESPATCHED 2005-02-04 14:52
> R3RTF BOOKING-IN 2005-04-05 14:33
> R3RTF DESPATCHED 2005-06-01 17:37
> Looking at these results what I need is the date difference between the
> BOOKING-IN and DESPATCHED dates. From the result set above the 3
> datediff (for days) values would be 23, 0 & 57.
> This is all part of a bigger result set and ideally I would like the
> sum of the 3 date differences shown in this example. (23 + 0 + 57 = 80)
> I am struggling to get this in a select statement, I have sampled using
> cursors but I'm not sure this is the way to go. Using a SELECT
> statement would be ideal.
> Any help would be much appreciated.
>