How to Search for Date and Time Values

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Brian Syverson » How to Search for Date and Time Values

How to Search for Date and Time Values

Rate Topic

Display Mode

Topic Options

Author

Message

bryansyverson

Posted Sunday, January 19, 2003 12:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2003 12:00 AM
Points: 2, Visits: 1

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp

Regards,
Bryan

Post #9381

pmanisekaran

Posted Monday, January 20, 2003 9:16 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 17, 2010 2:45 AM
Points: 17, Visits: 3

Why was "dtColumn like dt +'%' " not mentioned as one of the ways seraching for dates in s datetime column with inconsistent storage like storing date and time.

Post #51926

pmanisekaran

Posted Tuesday, January 21, 2003 12:03 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 17, 2010 2:45 AM
Points: 17, Visits: 3

hi abhi_develops,
I mean something like this, it obviously is not an efficient way. Just another method

ex:
SELECT CONVERT(varchar, dateEntered, 103)
FROM dbo.tblDirectory
WHERE CONVERT(varchar, dateEntered, 103) LIKE '09/07/2002%'

Post #51927

ckempste

Posted Tuesday, January 21, 2003 3:11 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1

Hi

Outstanding article that covers off some classic issues, thanks for spending the time writing it.

Cheers

Chris K
www.chriskempster.com

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Post #51928

Jonr

Posted Tuesday, January 21, 2003 4:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:34 AM
Points: 141, Visits: 61

Most excellent article Brian, one of those reference ones that will go into the very useful solutions folder. Thank you.

Jon Reade

Jon

Post #51929

bryansyverson

Posted Wednesday, January 22, 2003 10:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2003 12:00 AM
Points: 2, Visits: 1

Thanks for your comments.

You're right about the omission. However, as with most search expressions, there are many working solutions. I didn't mention
WHERE CONVERT(varchar, dateEntered, 103) LIKE '09/07/2002%'
nor did I mention
WHERE CAST(CAST(dateEntered AS char(11)) AS datetime) = '2002-09-07'
(I do show a few of these in Chapter 8 of my book, however.)

As you mentioned, the LIKE version will perform badly, perhaps even worse than the other version that just use functions. I figured there's no reason to include multiple poorly-performing solutions.

Regards,

Regards,
Bryan

Post #51930

JohnMarks

Posted Friday, January 24, 2003 3:20 PM

Valued Member

Group: General Forum Members
Last Login: Friday, June 14, 2013 6:41 AM
Points: 61, Visits: 421

Nice article. Just put your suggestions on how to calc time to good use for a report I had to write. Thanks

Post #51931

bambola

Posted Friday, March 07, 2003 4:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 09, 2009 12:56 PM
Points: 2, Visits: 13

<quote>
Datetime and smalldatetime are like the floating-point data types, float and real, in that they’re approximate numerics. That means the value retrieved from SQL Server may be different from the value that was originally stored.
</quote>

Reading the datetime as float will mostly result the correct values, but not always.

DECLARE @d datetime
SELECT @d = '1900-01-24 13:12:10.990'
SELECT @d as_datetime, convert(datetime, cast(@d as float)) as_float

Results are:
1900-01-24 13:12:10.990 as_datetime, 1900-01-24 13:12:10.987 as_float.

You can see that casting datetime to float and back result a value different from the initial one.
The reason is simple: Datetime and smalldatetime are not really floating point datatype. They are actually 2 4-bytes integers (or 2 2-bytes integers in case of smalldatetime). One represents the number of days past since 1900-01-01. The other, contains the number of clock ticks past since midnight (1 second = 300 clock ticks).

To calculate the integer containing the datepart, we could use the datediff function:

-- Calculate numner of days since 1900-01-01:
SELECT datediff(dd, 0, @d) as number_of_days
This will result 23 days.

Calculating the time part is trickier. We have to first calculate the number of second past since midnight, multiply is by 300 clock ticks and add the fraction of the milliseconds:

-- Calculate the number of clock ticks:
SELECT
(
datepart(hour,@d) * 60 * 60
+ datepart(minute,@d) * 60
+ datepart(second,@d)
) * 300
+ cast(datepart(ms,@d)/1000.*300 as int) as number_of_clock_ticks

This results 14259297 clock ticks.

Now let's try to read the 2 integers that are composing the datetime. I will cast the variable as binary(8), then convert to int the first 4 byes that represent the date part, and the last 4 bytes that represent the time part:

SELECT cast(substring(convert(binary(8), @d),1, 4) as int) date_part_in_int,
cast(substring(convert(binary(8), @d),5, 8) as int) time_part_in_int

The results are 23 days and 14259297 clock ticks which are the exact same values calculated above.

Note that for this example I used .990 as milliseconds to avoid rounding problems. Milliseconds are always rounded to .000 .003 or .007 in following way:

.990 .991 --> .990
.992 .993 ..994 --> .993
.995 .996 .997 .998 --> .997
.999 --> .000

DECLARE @d1 datetime, @d2 datetime
SELECT @d1 = '2002-02-02 23:59:59:998', @d2 = '2002-02-02 23:59:59:999'

SELECT @d1, @d2

Results: 2002-02-02 00:00:00.000 2002-02-03 00:00:00.000

This assignment might not have much sense. You are not likely to find yourself asigning
998 milliseconds to a datetime variable. But if you do, you can see that rounding occures before asigning the value. From this point on the value retrieved will never be different from the one asigned.

To see the difference between the conversion to float and int, try to run this script:

SET NOCOUNT ON
DECLARE @x datetime
DECLARE @t table
( base_date datetime
, as_float datetime
, as_binary binary(8)
)
DECLARE @i int
SELECT @x = '1900-01-01 13:12:10.999'
SELECT @i = 1
SELECT @x = dateadd(millisecond,@i,@x)

WHILE @i <= 10
BEGIN
SELECT @x = DATEADD(millisecond,@i,@x)
INSERT INTO @t
SELECT @x
, convert(datetime,cast(@x as float))
, convert(binary(8),@x)
SELECT @i = @i + 1
END

SELECT
base_date
, as_float
, case when as_float <> base_date then 'WRONG' else 'OK' end ok_f
, convert(datetime, as_binary) binary_to_date
, case when convert(datetime, as_binary) <> base_date then 'WRONG' else 'OK' end ok_b
FROM @t

There is not doubt that there are limitations to the datetime variable. I would have liked, for example, to be able to store values before 1753. I also cannot understand why millisecond are rounded and not stored correctly. However, as long as you are reading datetimes correctly, the system always returns the same value stored.

If you choose to refer to datetime at lower level, and avoid having the datetime algorytm run on all values (and I do it in some cases since performance can improve dramatically!), you must read it as 2 integers acording to the shown above. This is what it is - 2 integers. Not a float. Smile

A few words about searching. SQL Server does does not have seperate datatypes for date and time. And as long as this is the case (and from what I have heard in the next version there will be), it would be wrong to refer to datetime as date only.

When searching a date range, we have to make sure our time portion is set correctly. If time is irelevant, start_date time portion should be set to '00:00:00: and the end_date to '23:59:59.997'.

This can be done in serveral ways

- convert(varchar(10), start_date, 120)
(ISO is always the best way to handle dates)

- cast( substring(cast(start_date as binary(8), 1, 4) as datetime)
not that intuitive, but works much better than the above.

- dateadd(dd,datediff(dd,0,start_date ),0)
This is probably the most elegant and efficient way.

To set the end_date, you should first set the time part to zero, then add a day and substruct 2 milliseconds.
- dateadd(millisecond, -2, dateadd(dy,1,end_date_00))

I hope this shades some light on the datetime variable.

Bambola.

Edited by - bambola on 03/07/2003 04:30:07 AM

Post #51932

« Prev Topic | Next Topic »

Permissions