Results 1 to 4 of 4
  1. #1
    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,971
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2008
    Posts
    54
    Thank you So much Pat. We have the 2005 version. I really appreciate your help!

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    971
    There is a simpler way to lop off the time component:
    Code:
    CAST(FLOOR(CAST(DateTimeValue AS float)) AS DateTime)
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •