Getdate Function

[titans4771]

I am currently using getdate() in one of my queries as such:

where checkdate = getdate().. problem with this is it prints out the time: 2009-10-23 09:38:48.673


How can I set getdate() to not include the time:

2009-10-23 00:00:00.000

Thanks!!!

 

[gmmastros]

There are many ways. My Suggestion is...

Where CheckDate = DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[drlex]

I think this came up recently, and whilst it may not be the most intuitive solution,
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
is one of the quickest.

soi la, soi carré

 

[drlex]

Too slow to beat George; I'll go back to sleep in the sunshine.


soi la, soi carré

 

[gmmastros]

ppffttttt....

drlex, Why don't you take a shot at explaining HOW this works. If I like the explanation, I'll give ya a purply.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

I'll take a stab at it:

DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

DateTime columns are stored as integers in sqlserver. What you see when you query is a formatted version that we, as humans, can understand.

You have to read the statement from the inside out.

First, take note that Day 0 in sql server is:
1900-01-01 00:00:00.000 -- notice the time portion is 0

The inner portion:

DateDiff(Day, 0, GetDate())

will give you an interger that represents the current day.

Next, the outer portion:

DateAdd(Day, int that represents the current day ), 0)

This adds the current day(int) to 0(sql's first day). Since the firstday(0) has no time portion(all 0s), when we add today's value, we have no time portion(all 0s).

 

[gmmastros]

jbenson001,

Your explanation is not quite right. First of all, SQL Server stores datetime as a pair of integers.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server

However, it often helps to think of DateTime's as floating point numbers. The whole number part would represent the numbers of days that have elapsed since Jan 1, 1900, and the fraction part would represent the time (0.25 = 8 AM, 0.5 = Noon, 0.75 = 8 PM).

For example:

Select Convert(Float, GetDate()), GetDate()

If we convert GetDate() to float, we can see the whole number and the fraction part. Another way to remove the time component is to convert to float, take the Floor, and convert back to datetime. Like this:

Select Convert(DateTime, Floor(Convert(Float, GetDate())))

This method performs almost as well as the DateAdd/DateDiff method. In fact, you have to try pretty hard to notice a difference.

The real key to this method is how DateDiff works. DateDiff returns an integer representing the number of TRANSITIONS based on the interval (the first parameter). For example, The difference between 11:59:59 PM and 12:00:01 AM is 2 minutes, but DateDiff(day will report 1 day because the transition occurs exactly at midnight.

So... DateDiff returns an INTEGER number of days that have elapsed since Jan 1, 1900. We then add this number of days to Jan 1, 1900. Since we already have an integer from the DateDiff, the DateAdd will add an integer number of days to a date without time. In this case, the date without time is the 0 (which represents Jan 1, 1900 00:00:00).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

I knew I shouldn't have posted.. LOL
Thanks for the explination George

 

[gmmastros]

No. I'm glad you did. I bet you learned something in the process of explaining it. I encourage you to do this more often. Besides... you were mostly right.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[drlex]

I was just about to post all that; late again to the party.
Well, all it remains for me to say is "Happy Friday, y'all!"

soi là, soi carré

 

[jbenson001]

Yeah, going through it did help. And I always like to learn somehthing new. Thanks again George.

 

[Emtucifor]

It doesn't matter if SQL Server stores dates internally as hulzoid interdimensional gravity spaces, the datediff function method will work.

However, you might consider avoiding floor(), and also think hard about using direct arithmetic on dates (ala GetDate() - 1) because the Date and Datetime2 data types in SQL Server 2008 won't let you perform these operations on them. Plus, they store their data differently. Date has a different "zero date" than Datetime and it is stored little-endian instead of big-endian.

Look:

DECLARE @d Date
SET @d = '20091023'
SELECT @d + 1
Server: Msg 206, Level 16, State 2, Line 3
Operand type clash: date is incompatible with int


DECLARE @d date
SET @d = '00010101'
SELECT convert(binary(3),@d)

--0x000000

DECLARe @d date
SET @d ='99990101'
SELECT convert(binary(3),@d)

--0x6EB837

That's reverse byte order in case you missed it...
0x37B86E = 3651694 days = 99998 years
0x6EB837 = 7256119 days = ~19866.7 years

 

[JeffModen]

I'm pretty sure that BOL is incorrect when it says that DATETIME consists of two integers... instead, I believe that DATETIME is FLOAT with a fixed Exponent...

DECLARE @DateTime DATETIME
    SET @DateTime = '23:59:59.997'

SELECT CAST(CAST(@DateTime AS FLOAT) AS DATETIME), 
       CAST(CAST(@DateTime AS DECIMAL(38,38)) AS DATETIME)

Results...

(No column name)	(No column name)
1900-01-01 23:59:59.997	1900-01-01 23:59:59.993

If it were simply two integers, the conversion to DECIMAL and back to DATETIME would work correctly.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

Jeff,

I just posted about this recently: SQL Server Inner Storage of datetime Data Type

 

[markros]

Do you think this is incorrect

http://dotnetsamplechapters.blogspot.com/2007/09/how-are-dates-stored-internally-in-sql.html

?

 

[drlex]

I got confused when the article described a tick as 1/300th of a second and then wrote:

SELECT CONVERT(DATETIME,0x000000000000012C) -- 1 minute = 300 ticks

soi là, soi carré

 

[JeffModen]

You guys can say and do what you want... the FLOAT conversions work and the DECIMAL conversions work only as well as they would against a FLOAT data type. If it helps you to think of it as two INTEGERs as MS suggests, then knock yourself out. Just don't ever tell anyone to do fractional Decimal math against datetimes because it will eventually fail you. It's gotta be FLOAT instead.

Heh... of course, we all use date functions for that, right? (Not)

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[JeffModen]

It's also easier for a lot of folks to think of the datetime datatype simply as the number of whole and fractional days since midnight on 1900-01-01 rather than two integers just like it's easier for people to think in Decimal numbers rather than the Binary representation of their nearest FLOAT equivalent. ;-)

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

Finally, here is definitive proof that datetime is stored as two 4-byte integers.

drlex, you're right, that article should have said "1 *second* = 300 ticks".

You guys can say and do what you want... the FLOAT conversions work and the DECIMAL conversions work only as well as they would against a FLOAT data type. If it helps you to think of it as two INTEGERs as MS suggests, then knock yourself out. Just don't ever tell anyone to do fractional Decimal math against datetimes because it will eventually fail you. It's gotta be FLOAT instead. It's also easier for a lot of folks to think of the datetime datatype simply as the number of whole and fractional days since midnight on 1900-01-01 rather than two integers just like it's easier for people to think in Decimal numbers rather than the Binary representation of their nearest FLOAT equivalent.

It doesn't "help" me to think of it that way, I think of it that way because that's how it is. I prefer to think how things are. Other ways of thinking can at times be useful, but when they are inaccurate and don't offer any advantage, I say don't use them and also: train yourself to think about the thing accurately instead.

As for doing math on date types, I recommend that no one ever try to convert them to a number with a fraction (decimal, float, or any other data type). I think float has its own problems, too! Just because decimal has problems doesn't mean float is what should be used. Don't use either! If we're going to give advice at all, let's give the best advice.

 

[JeffModen]

So explain why the float conversion works and the decimal conversion does not.

Nah... never mind.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row