Get DATE part of the DATETIME

Question

Post reply

Get DATE part of the DATETIME

Jano Petras

Old Hand

Points: 327
More actions
December 12, 2007 at 11:39 pm

#201723

Comments posted to this topic are about the item Get DATE part of the DATETIME

Viewing 15 posts - 1 through 15 (of 44 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Another way to do this that I learned on SSC, and just as easy is this:

declare @DateOnly datetime

set @DateOnly = dateadd(dd,datediff(dd, 0, getdate()), 0)

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Skinning a cat innit:

SELECT [DateOnly] = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) -- CAST / FLOOR / CAST

SELECT [DateOnly] = dateadd(dd,datediff(dd, 0, getdate()), 0) -- DATEADD / DATEDIFF

SELECT [DateOnly] = CAST(CAST(GETDATE() AS INT) AS DATETIME) -- CAST / CAST

SELECT [DateOnly] = CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME) -- CAST / CONVERT

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

majorbloodnock SSCrazy Eights Points: 9389 More actions · Answer 3

[Pedantry mode]

In the explanation that went with Jano's code, surely 0.25 should be 06:00am and 0.75 should be 18:00pm instead of 4am and 4pm respectively

[/Pedantry mode]

Not that I care. Good solutions to a problem I see quite often. Useful script.

Semper in excretia, suus solum profundum variat

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 4

Does anyone know which uses least processing time?

Derek

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Not yet, Derek. Derek? Where'd he go? 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 6

I ran a few tests and found that there are only marginal differences beween most of them, except that the dateadd/datediff form takes about a third longer (on my test 40 microsecs for 100,000 recs instead of around 30).

Derek

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Running an update against a table with a little over 500,000 rows I get the following (average of three runs, in seconds)

1) CAST / FLOOR / CAST [2.32]

2) DATEADD / DATEDIFF [1.84]

3) CAST / CAST [1.69]

4) CAST / CONVERT [4.33]

So not much in it except for method 4.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Walker Right there with Babe Points: 756 More actions · Answer 8

I used the Cast/Floor/Cast method myself, although often it's just Floor/Cast, since integers are implicitly converted to datetime. (For this reason, on some tables, I store an int for date instead of datetime).

The Cast/Cast method, will not work properly, since after 12:00pm, it'll round to the next day. (See BOL, datetime to int casting will round, not truncate).

Of course, with 2008 coming out with a date only data type, these fun workarounds will hopefully become obsolete.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

True, In SQL 2008 you can get the date portion just by assigning a datetime value to a variable of the DATE type.

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

DECLARE @TheDate DATETIME

SET @TheDate = '2008-02-05 23:59:59.997'

SELECT @TheDate

SELECT CAST(CAST(@TheDate-0.5000000385803 AS INT) AS DATETIME)

SET @TheDate = '2008-02-05 00:00:00.000'

SELECT CAST(CAST(@TheDate-0.5000000385803 AS INT) AS DATETIME)

😉

We're still on 2K, could be decades before we see 2K8 :sick:

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bc_ Hall of Fame Points: 3168 More actions · Answer 11

it doesn't wait until noon to start rounding up...

DECLARE @TheDate DATETIME

SET@TheDate = '2008-02-05 11:59:59.993'

SELECT@TheDate

SELECTCAST(@TheDate AS INT)

SELECTCAST(CAST(@TheDate AS INT) AS DATETIME)

SET@TheDate = '2008-02-05 11:59:59.997'

SELECT@TheDate

SELECTCAST(@TheDate AS INT)

SELECTCAST(CAST(@TheDate AS INT) AS DATETIME)

[font="Arial Narrow"]bc[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

DECLARE @TheDate DATETIME, @HalfDayAndaBit DECIMAL (14,13)

SET @HalfDayAndaBit = 0.5000000385803

SET @TheDate = '2008-02-05 11:59:59.993'

SELECT @TheDate

SELECT CAST(@TheDate-@HalfDayAndaBit AS INT)

SELECT CAST(CAST(@TheDate-@HalfDayAndaBit AS INT) AS DATETIME)

SET @TheDate = '2008-02-05 11:59:59.997'

SELECT @TheDate

SELECT CAST(@TheDate-@HalfDayAndaBit AS INT)

SELECT CAST(CAST(@TheDate-@HalfDayAndaBit AS INT) AS DATETIME)

That's where the 'AndaBit' comes in.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ryan Davidson SSC Rookie Points: 29 More actions · Answer 13

If you don't care about the date coming back as an string. Then this is good solution too.

PRINT LEFT(getdate(), 11)

dbajunior SSCrazy Points: 2307 More actions · Answer 14

dbajunior

SSCrazy

Points: 2307

February 5, 2008 at 2:24 pm

#775341

or...

Select convert(char(10),getdate(),101)