Post reply

altering column from datetime to varchar eliminates seconds and milliseconds part

  • January 18, 2005 at 8:46 am

    #163516

     

    Friends,

    I have a table with datetime column, when i alter the column to varchar I'm losing seconds and milliseconds. Could any one suggest if there is a way to alter the column to varchar without losing seconds and milliseconds part.

    I don't prefer to add column and do updates etc etc......

    Thanks in advance

     

     

     

     

  • January 18, 2005 at 9:06 am

    #537862

    Try this way

    select convert(varchar(25),getdate(),21)

    Thanks,

    Ganesh

     

  • January 18, 2005 at 9:45 am

    #537872

    Here is my situation,

    tOrders(dtmOrdered datetime)

    Alter table tOrders alter column dtmOrdered varchar(26)

    when I do this:

    2005-01-18 10:34:50.677 is changed to Jan 18 2005 10:34AM, it's not preserving seconds and milliseconds part, I guess it's default behaviour.

    Is there a way to preserve existing data without losing seconds and milliseconds

     

     

     

  • January 18, 2005 at 10:25 am

    #537892

    Add a column, insert the data and then remove the original column.  Question is though WHAT importance are seconds/milliseconds AND WHY do you need to convert this to VARCHAR instead of performing a FORMAT during output?

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • January 19, 2005 at 1:46 am

    #537994

    check out the ISO8601 style of the convert function. This will give you everything in an internationally recognised textual format.

     select convert(varchar,getdate(),126)

    = 2005-01-19T08:35:05.507

    That's the format I prefer.

     

    regards,

    Mark Baekdal

    http://www.dbghost.com

    +44 (0)208 241 1762

    Database change management for SQL Server

     

     

     

  • January 19, 2005 at 2:03 am

    #537997

    or you could use this:

    select convert(varchar(12),getdate()) + right(convert(varchar,getdate(),126),len(convert(varchar,getdate(),126))-charindex('T',convert(varchar,getdate(),126)))

    result = Jan 19 2005 08:48:19.397

    depending on your preferencing for displaying the date to your users.

     

     

    regards,

    Mark Baekdal

    http://www.dbghost.com

    +44 (0)208 241 1762

    Database change management for SQL Server

     

     

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply