Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Get DATE part of the DATETIME Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, December 12, 2007 11:39 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, September 1, 2014 9:32 PM Points: 5, Visits: 16
 Comments posted to this topic are about the item Get DATE part of the DATETIME
Post #432670
 Posted Monday, February 4, 2008 10:56 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 12:10 PM Points: 23,516, Visits: 37,739
 Another way to do this that I learned on SSC, and just as easy is this:declare @DateOnly datetimeset @DateOnly = dateadd(dd,datediff(dd, 0, getdate()), 0)
Post #451447
 Posted Tuesday, February 5, 2008 2:17 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 Skinning a cat innit:`SELECT [DateOnly] = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) -- CAST / FLOOR / CASTSELECT [DateOnly] = dateadd(dd,datediff(dd, 0, getdate()), 0) -- DATEADD / DATEDIFFSELECT [DateOnly] = CAST(CAST(GETDATE() AS INT) AS DATETIME) -- CAST / CASTSELECT [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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #451493
 Posted Tuesday, February 5, 2008 3:44 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, September 28, 2016 9:46 AM Points: 1,055, Visits: 3,062
 [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 5, 2008 3:46 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, February 22, 2016 8:32 AM Points: 1,274, Visits: 1,992
 Does anyone know which uses least processing time? Derek
Post #451515
 Posted Tuesday, February 5, 2008 3:54 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #451516
 Posted Tuesday, February 5, 2008 4:01 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, February 22, 2016 8:32 AM Points: 1,274, Visits: 1,992
 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 5, 2008 4:29 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #451533
 Posted Tuesday, February 5, 2008 6:35 AM
 SSChasing Mays Group: General Forum Members Last Login: Thursday, September 22, 2016 7:06 AM Points: 630, Visits: 413
 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 5, 2008 7:03 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 12:10 PM Points: 23,516, Visits: 37,739
 True, In SQL 2008 you can get the date portion just by assigning a datetime value to a variable of the DATE type.
Post #451616

 Permissions