Datetime Conversion Question

KevinYC · #1 (**permalink**) 11-28-12, 15:31

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

Pat Phelan · #2 (**permalink**) 11-28-12, 17:54

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:

Code:

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)

-PatP

KevinYC · #3 (**permalink**) 11-30-12, 17:04

Thank you So much Pat. We have the 2005 version. I really appreciate your help!

weejas · #4 (**permalink**) 12-03-12, 09:08

There is a simpler way to lop off the time component:

Code:

CAST(FLOOR(CAST(DateTimeValue AS float)) AS DateTime)

Internet Services

Web Development

Digital Marketing

Consumer Tech