SQL Server Performance Forum – Threads Archive

Extracting Date from datetime

Hi all, I would like to create a couple of variables startdate (08/08/04 00:00) and an enddate (08/08/04 23:59) to then use in a Where xxxdate Between @startdate and @enddate. The way I would go about this is to strip out the date from the getdate() function, tack on the time (00:00 or 23:59) and stuff it into the variables. The question is… Is there a T-sql function that provides JUST the date? or, for curiosity, JUST the time? or is it a do it yourself… must be some lazydba tendencies here <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Thank you. ELL

SQL Server 2000 does not have separate data types for date and time. However there are a number of functions like DATEPART and CONVERT which allow an amazing amount of manipulation. You can create a DATETIME varaible and use these functions to extract just date or just time from somewhere else and do arithmetic – adding subtracting date and time. Tryhttp://www.sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

I have two functions for this, GetDateOnly() and GetTimeOnly(). Use the following idea to create yours: SELECT GetDate(),
Cast(DatePart(MM, GetDate()) AS VarChar(2))+’/’+Cast(DatePart(DD, GetDate()) AS VarChar(2))+’/’+Cast(DatePart(YY, GetDate()) AS VarChar(4)),
Cast(DatePart(Hour, GetDate()) As VarChar(2))+’:’+Cast(DatePart(Minute, GetDate()) As VarChar(2))+’:’+Cast(DatePart(Second, GetDate()) As VarChar(2))
CanadaDBA

GetDateOnly() and GetTimeOnly() … sweet functions.
I’ll use them. Thank you much!
Ell

I wouldn’t use functions for this. They turn your operation effectively into a row by row processing, so it’s not a set operation anymore. Also, the link mentioned isn’t the best around on datetimes. I like this one<a target="_blank" href=http://www.karaszi.com/sqlserver/info_datetime.asp>http://www.karaszi.com/sqlserver/info_datetime.asp</a> by SQL Server MVP Tibor Karaszi more. Sure, it is perfectly valid to use some CONVERT logic to get rid of the time, respectively time portion of a datetime. However, when you look at the internal format in which such a datetime is stored, you’ll notice that it is a binary(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />. So methods using integer or binary operations should be faster. I have several ideas how to strip off time on my site here<a target="_blank" href=http://www.insidesql.de/content/view/113/>http://www.insidesql.de/content/view/113/</a> If you’re looking for sheer performance I would use <pre> SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0) SELECT CAST(CAST(SUBSTRING(CAST(GETDATE() AS binary(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),1,4) AS INT) AS DATETIME) </pre> maybe <pre> SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),1,4) + 0x00000000 AS DATETIME) </pre> Also, I wouldn’t attach the time back again, but rather add a + 1 on GETDATE(). When you don’t supply a time, it defaults to midnight, which is just a second more than what you get when you add the time yourself. That way there shouldn’t be a need for a solution that gives you the time only. However, here<a target="_blank" href=http://www.insidesql.de/content/view/271/>http://www.insidesql.de/content/view/271/</a> are some ideas on how to strip the date from a datetime. There might be other solution around, but since I consider this a rather odd requirement, I haven’t digged too deep into this. HTH –Frank http://www.insidesql.de</a>

These codes generates date as 2004-08-12 00:00:00.000 but still there is a time part in this format. It’s not bad idea to get benefit of binary format to speed up extracting date or time portion of a DATETIME variable. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis <pre> SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0) SELECT CAST(CAST(SUBSTRING(CAST(GETDATE() AS binary(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),1,4) AS INT) AS DATETIME) </pre> maybe <pre> SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),1,4) + 0x00000000 AS DATETIME) </pre> <hr height="1" noshade id="quote"></blockquote id="quote"> CanadaDBA

Sure it has a time portion. You can’t have a datetime without both date and time. This might (or will) change in the next version finally. However, setting this time portion to midnight (00:00:00.000) does the job quite nicely. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] –Frank http://www.insidesql.de</a>

select convert(char(10),getdate(),101)
gives date perfectly….if thass all you want…see BOL for more options HTH Harsh

It’s nice. What is the logic behind that? Where can I find it in BOL?

quote:Originally posted by harsh_sr select convert(char(10),getdate(),101)
gives date perfectly….if thass all you want…see BOL for more options HTH Harsh

CanadaDBA

It is mentioned in CAST and CONVERT, although I believe there are far more you can switch to as there are listed. –Frank
http://www.insidesql.de

Right Whole lot of options are available in CAST & CONVERT on BOL. Harsh

Here are the CONVERT options. One of these should work. They’re much easier than doing the whole substring thing. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> <pre> SET NOCOUNT ON DECLARE @min INT, @max INT, @date DATETIME SELECT @min = 1, @max = 131, @date = GETDATE() SELECT @date WHILE @min <= @max BEGIN IF @min BETWEEN 15 AND 19 OR @min = 26 OR @min BETWEEN 27 AND 99 OR @min BETWEEN 115 AND 119 OR @min BETWEEN 122 AND 125 OR @min BETWEEN 127 AND 129 BEGIN GOTO NEXT_LOOP END SELECT @min, CONVERT(VARCHAR,@date,@min) NEXT_LOOP: SELECT @min = @min + 1 END </pre> MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Thank you Frank and Harsh. It was interesting. I have never studied Convert and Cast in detail so, I was not aware of this. And thank you Derrick for the code. It is valuable that knowledgeable people share their knowledge professionally here. CanadaDBA

you can not use convert because your xxxdate is datetime and it has time in it anyway Where xxxdate Between convert(char(10),@startdate,101) and convert(char(10),@enddate,101) the best solution is like FarhadR’s: where xxdate betweeen – your start date plus zero time dateadd(dd,datediff(dd,0,@startdate),0) and – your end date plus 1 day minus 2 millisecond – enddate: YYYY-MM-DD 23:59:59.997 dateadd(millisecond, -2, dateadd(dy,1,@enddate)) or use some variants of cast(substring(cast(@startdate as binary(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />), 1, 4) my question is which way is more effective from sql server performance point of view: dateadd or substring? valko

Not to sound combative, but I think I’ve mentioned your points already earlier in the thread. As for your question: Both methods (integer and binary) are very effective, so wouldn’t be too much concerned with tweaking the last milliseconds out of it. After all, what do you get when this part of the code is efficient, but the rest is poor? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – –Frank http://www.insidesql.de</a>

]]>