locked
date conversion problem RRS feed

  • Question

  • Hi,

    In my database have a table xxx abd column x_date, the value stored in this column formate like (20011201). And i want to convert it to format(2001-12-01 12:31:54).

    We are using sql server 2005, please any inbuld function we can use.

    Regs,

    brijesh

     

    Friday, December 2, 2011 7:18 AM

Answers

  • If you don't have a time in your column, you only get 2011-12-01 00:00:000

    And I recomment to use convert because of adding the style:

    Declare @date varchar(20)
    set @date = '20111201 12:31:54'
    select convert(datetime,@date,112)

    ==>> 2011-12-01 12:31:54.000

    • Proposed as answer by skc_chat Friday, December 2, 2011 8:00 AM
    • Marked as answer by Kalman Toth Friday, December 9, 2011 7:12 AM
    Friday, December 2, 2011 7:39 AM

All replies

  • You can try this:

    DECLARE @Date VARCHAR(30)
    SET @Date = '20011201'

    SELECT CAST(@Date AS DATETIME)
    Shatrughna.
    Friday, December 2, 2011 7:22 AM
  • If you don't have a time in your column, you only get 2011-12-01 00:00:000

    And I recomment to use convert because of adding the style:

    Declare @date varchar(20)
    set @date = '20111201 12:31:54'
    select convert(datetime,@date,112)

    ==>> 2011-12-01 12:31:54.000

    • Proposed as answer by skc_chat Friday, December 2, 2011 8:00 AM
    • Marked as answer by Kalman Toth Friday, December 9, 2011 7:12 AM
    Friday, December 2, 2011 7:39 AM
  • you should use convert function it fit ur requirement here.
    Friday, December 2, 2011 8:01 AM
  • The following article deals also with datetime conversion:

    http://www.sqlusa.com/bestpractices/datetimeconversion/

    Note that the internal representation if datetime is:  date & time (8 bytes special format). All the string variations in various languages are software generated external representations. For example, SSMS generates the following string format:

    SELECT CURRENT_TIMESTAMP
    -- 2011-12-02 03:47:10.533
    

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

    • Edited by Kalman Toth Friday, December 2, 2011 8:48 AM
    Friday, December 2, 2011 8:37 AM