Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Get DATE part of the DATETIME Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2007 11:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 01, 2013 7:37 AM
Points: 5, Visits: 15
Comments posted to this topic are about the item Get DATE part of the DATETIME
Post #432670
Posted Monday, February 04, 2008 10:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 22,492, Visits: 30,192
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)




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
Posted Tuesday, February 05, 2008 2:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Posted Tuesday, February 05, 2008 3:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 13, 2014 4:35 AM
Points: 1,046, Visits: 2,997
[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
Posted Tuesday, February 05, 2008 3:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
Does anyone know which uses least processing time?


Derek
Post #451515
Posted Tuesday, February 05, 2008 3:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Exploring Recursive CTEs by Example Dwain Camps
Post #451516
Posted Tuesday, February 05, 2008 4:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Posted Tuesday, February 05, 2008 4:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Exploring Recursive CTEs by Example Dwain Camps
Post #451533
Posted Tuesday, February 05, 2008 6:35 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 03, 2014 11:28 AM
Points: 630, Visits: 351
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
Posted Tuesday, February 05, 2008 7:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 22,492, Visits: 30,192
True, In SQL 2008 you can get the date portion just by assigning a datetime value to a variable of the DATE type.




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 #451616
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse