Get Date only from getdate() function

Question

Post reply

Get Date only from getdate() function

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

SwePeso (4/3/2010)
Don't forget what happens to dates with time portion between 12:00 and 24:00 (pm times)...

With which method Peter?

FLOAT -> INT is a truncate, so should be ok?

DATETIME -> INT is rounded, and would be a problem, but the DATEADD/DATEDIFF doesn't use that.

Help?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 2

The DATEADD/DATEDIFF method (using a variable for layout purposes):

DECLARE @Base DATETIME;

SET @Base = CONVERT(DATETIME, '1900-01-01T00:00:00.000', 126);

SELECT DATEADD(DAY, (DATEDIFF(DAY, @Base, GETDATE())), @Base);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

The DATEDIFF/DATEADD is the only reliable way to get accurate results.

See what happens with pre-1900 dates using FLOAT method

DECLARE@Today DATETIME = '1898-12-31 23:00'

SELECTCAST(CONVERT(FLOAT, @Today) AS INT),

DATEDIFF(DAY, 0, @Today)

N 56°04'39.16"
E 12°55'05.25"

Paul White SSC Guru Points: 150467 More actions · Answer 4

SwePeso (4/3/2010)
See what happens with pre-1900 dates using FLOAT method...

Hmmm...nasty!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Paul White NZ (4/4/2010)
SwePeso (4/3/2010)
See what happens with pre-1900 dates using FLOAT method...
Hmmm...nasty!

Don't blame me. Blame rounding... 😀

N 56°04'39.16"
E 12°55'05.25"

wallace.dave Valued Member Points: 56 More actions · Answer 6

Apologies for not seeing the undoubted flaws in this one (excuse the pun :-D). I got this from blog on this topic I have long since lost the link. I thnk it is a good solution if you work (like me) in an environment where you must try wherever possible to adhere to cross platform standards - I'm sure some of the more experiened folk here will quickly correct me!:

CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)

This ANSI definition of the FLOOR function works around the rounding errors seen on the negative dates by using 'round down' as its logic rather than 'round nearest' that is implied by CONVERT.

Thus, you get the following when replicating the example above:

DECLARE @Today DATETIME = '1898-12-31 23:00'

SELECT FLOOR(CAST(@Today AS FLOAT)), DATEDIFF(day,0,@Today)

Dave

Paul White SSC Guru Points: 150467 More actions · Answer 7

wallace.dave (4/5/2010)
Apologies for not seeing the undoubted flaws in this one (excuse the pun :-D). I got this from blog on this topic I have long since lost the link. I thnk it is a good solution if you work (like me) in an environment where you must try wherever possible to adhere to cross platform standards - I'm sure some of the more experiened folk here will quickly correct me!

Hey Dave,

The FLOOR construction certainly works - and it is the usual form (rather than CASTing to a INT) for exactly that reason.

It shares all the other problems though (relies on implementation detail, non-deterministic, cannot be persisted pre-2008, only works with one of the date/time types...and so on.)

Please don't get me started on why insistence on so-called cross-platform 'standards' is such wrong-headed thinking!

As an aside, one question that intrigues me is how to format a date/time as a string in a cross-platform manner...?

edit: Shouldn't GetDate() be CURRENT_TIMESTAMP? 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 8

One other daft method I have seen from time to time:

SELECT DATEADD

(

DAY,

CONVERT

(

INTEGER,

SUBSTRING

(

CONVERT(BINARY(8), CURRENT_TIMESTAMP)

, 1, 4)

)

, CONVERT(DATETIME, '1900-01-01T00:00:00.000', 126)

);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

brosspaxedi SSCommitted Points: 1591 More actions · Answer 9

Do geeks always have this much trouble getting a date?!

*******************
What I lack in youth, I make up for in immaturity!

Paul White SSC Guru Points: 150467 More actions · Answer 10

bross 52202 (4/5/2010)
Do geeks always have this much trouble getting a date?!

LMAO

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 11

Paul White NZ (4/1/2010)
If there are points available for being obscure...
SELECT {fn CURRENT_DATE()};

This clearly wins the simplicity contest. I have not seen this one before, thanks.

Paul White SSC Guru Points: 150467 More actions · Answer 12

dbowlin (4/5/2010)
Paul White NZ (4/1/2010)
If there are points available for being obscure...
SELECT {fn CURRENT_DATE()};
This clearly wins the simplicity contest. I have not seen this one before, thanks.

No worries - but be apprised that it is non-deterministic and relatively slow. It uses string-based conversion internally, and is exactly the same as writing:

SELECT CONVERT(varchar(10),getdate(),23)

Notice that style 23 is undocumented.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 13

wallace.dave (4/5/2010)
I thnk it is a good solution if you work (like me) in an environment where you must try wherever possible to adhere to cross platform standards - I'm sure some of the more experiened folk here will quickly correct me!:

Heh... I agree with Paul... you don't even want to get me started on the huge mistake that trying to adhere to cross platform standards is. I will summarize though... you've just bought the scientific calculator that you've always wanted and you take it to work where your boss says that not everyone has such a calculator and that you must use only the basic 4 math functions. Yeah, right... I'm going to follow that rule... I'll find a new job first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Terri-92562 SSCommitted Points: 1614 More actions · Answer 14

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0).

From the article in today's post:

http://www.sqlservercentral.com/articles/Date+Manipulation/69694/

Terri

To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]