none
Convert DateTime to a DateTime with Milliseconds format

    Question

  • Hi,

    I am trying to access a date column up to  millisecond precession. So I cast date to as follows:

    Code Block
    CONVERT(varchar(23),CREATE_DATE,121)

    I get millisecond part as a result of query but it’s “000”.

    When I try to test the format by using getDate instead of DateTime column I get right milliseconds.

    CONVERT(varchar(23),GetDate(),121) --Gives right milliseconds in return


    Monday, November 05, 2007 9:16 PM

Answers

  • What is the datatype of the CREATE_DATE?  IS there milliseconds stored in the column at all?  What is the result of the query when you do:

     

    select create_date

    from    tablename

     

    ?

    Monday, November 05, 2007 9:58 PM
  • I'm agreed that this is most likely a data type issue. Below are two examples using different data types. The datetime data type works fine.

     

    Code Block

    --**** smalldatetime ****--

    DECLARE @smalldatetime SMALLDATETIME

    SET @smalldatetime = GETDATE()

    --121 ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)

    SELECT CONVERT(VARCHAR(23), @smalldatetime, 121)

     

    --**** datetime ****--

    DECLARE @datetime DATETIME

    SET @datetime = GETDATE()

    --121 ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)

    SELECT CONVERT(VARCHAR(23), @datetime, 121)

     

     

     

    Monday, November 05, 2007 10:26 PM

All replies

  • What is the datatype of the CREATE_DATE?  IS there milliseconds stored in the column at all?  What is the result of the query when you do:

     

    select create_date

    from    tablename

     

    ?

    Monday, November 05, 2007 9:58 PM
  • I'm agreed that this is most likely a data type issue. Below are two examples using different data types. The datetime data type works fine.

     

    Code Block

    --**** smalldatetime ****--

    DECLARE @smalldatetime SMALLDATETIME

    SET @smalldatetime = GETDATE()

    --121 ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)

    SELECT CONVERT(VARCHAR(23), @smalldatetime, 121)

     

    --**** datetime ****--

    DECLARE @datetime DATETIME

    SET @datetime = GETDATE()

    --121 ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)

    SELECT CONVERT(VARCHAR(23), @datetime, 121)

     

     

     

    Monday, November 05, 2007 10:26 PM