Get DATE part of the DATETIME

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 Jano Petras » Get DATE part of the DATETIME

45 posts, Page 1 of 5

1 2 3 4 5 »»»

Get DATE part of the DATETIME

Rate Topic

Display Mode

Topic Options

Author

Message

Jano Petras

Posted Wednesday, December 12, 2007 11:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 19, 2012 4:49 AM
Points: 5, Visits: 14

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

Post #432670

Lynn Pettis

Posted Monday, February 04, 2008 10:56 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617, Visits: 27,450

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)

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #451447

ChrisM@Work

Posted Tuesday, February 05, 2008 2:17 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:32 AM
Points: 5,613, Visits: 10,981

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
Exploring Recursive CTEs by Example Dwain Camps

Post #451493

majorbloodnock

Posted Tuesday, February 05, 2008 3:44 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 9:44 AM
Points: 1,043, Visits: 2,943

[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, sumus solum profundum variat

Post #451513

Derek Dongray

Posted Tuesday, February 05, 2008 3:46 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

Does anyone know which uses least processing time?

Derek

Post #451515

ChrisM@Work

Posted Tuesday, February 05, 2008 3:54 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:32 AM
Points: 5,613, Visits: 10,981

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}

Post #451516

Derek Dongray

Posted Tuesday, February 05, 2008 4:01 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

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

Post #451518

ChrisM@Work

Posted Tuesday, February 05, 2008 4:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:32 AM
Points: 5,613, Visits: 10,981

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}

Post #451533

Sean Walker

Posted Tuesday, February 05, 2008 6:35 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:00 PM
Points: 630, Visits: 279

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.

Post #451602

Lynn Pettis

Posted Tuesday, February 05, 2008 7:03 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617, Visits: 27,450

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

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #451616

« Prev Topic | Next Topic »

45 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions