If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Microsoft SQL Server > Datetime Conversion Question

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Aug 2008
Posts: 54
Datetime Conversion Question

Hello Everyone,

Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?

The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.

Any input is greatly appreciated

convert(char(15), process_date,112) as process_dt
Reply With Quote
  #2 (permalink)  
Resident Curmudgeon
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,815
There are two ways to accomplish this goal. If you are running SQL 2008 or later, the simplest and the fastest solution is just to convert to the DATE datatype. If you are running SQL 2005 or earlier, you have to do a bit more funky dance:
DECLARE @a      DATETIME = GetDate()
,  @b           DATETIME
,  @i           INT = 0
,  @j           INT = 0
,  @limit       INT = 10000000
,  @d1          DATETIME2
,  @d2          DATETIME2
,  @d3          DATETIME2

SET @d1 = GetDate()

WHILE @i < @limit
    SELECT @i += 1, @b = Cast(@a AS DATE)

SET @d2 = GetDate()

WHILE @j < @limit
    SELECT @j += 1, @b = DateAdd(day, 0, DateDiff(day, 0, @a))

SET @d3 = GetDate()

SELECT @a, @b
,  DateDiff(ms, @d1, @d2)
,  DateDiff(ms, @d2, @d3)
,  DateDiff(ms, @d2, @d3) - DateDiff(ms, @d1, @d2)
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #3 (permalink)  
Registered User
Join Date: Aug 2008
Posts: 54
Thank you So much Pat. We have the 2005 version. I really appreciate your help!
Reply With Quote
  #4 (permalink)  
Registered User
Join Date: Sep 2006
Location: Surrey, UK
Posts: 970
There is a simpler way to lop off the time component:
CAST(FLOOR(CAST(DateTimeValue AS float)) AS DateTime)
10% of magic is knowing something that no-one else does. The rest is misdirection.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On