## Get DATE part of the DATETIME

 Author Message Jano Petras Grasshopper Group: General Forum Members Points: 13 Visits: 16 Comments posted to this topic are about the item Get DATE part of the DATETIME Lynn Pettis One Orange Chip Group: General Forum Members Points: 27949 Visits: 38162 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) Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) ChrisM@Work SSChampion Group: General Forum Members Points: 11034 Visits: 19256 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 majorbloodnock Ten Centuries Group: General Forum Members Points: 1173 Visits: 3062 [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 StarNamer Ten Centuries Group: General Forum Members Points: 1372 Visits: 1992 Does anyone know which uses least processing time? Derek ChrisM@Work SSChampion Group: General Forum Members Points: 11034 Visits: 19256 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 StarNamer Ten Centuries Group: General Forum Members Points: 1372 Visits: 1992 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 SSChampion Group: General Forum Members Points: 11034 Visits: 19256 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 Sean Walker SSChasing Mays Group: General Forum Members Points: 638 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. Lynn Pettis One Orange Chip Group: General Forum Members Points: 27949 Visits: 38162 True, In SQL 2008 you can get the date portion just by assigning a datetime value to a variable of the DATE type. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)