Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008

    Unanswered: 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
    In front of the computer
    Provided Answers: 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:
    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.

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

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 7
    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.
    Beers earned: 1

Posting Permissions

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