none
What is the appropriate data type?

    Question

  • Hi what is appropriate data type in sql server to store date and time along with timezone? I should be able to share time like the following

    21-03-1977 22:33:33 IST

     

    Thanks in advance

    Saturday, March 27, 2010 7:10 AM

Answers

  • Hi,

    If you are using SQL 2008 you can use the datetimeoffset data type.

    Please find the following link ; - http://blogs.msdn.com/sqlprogrammability/archive/2008/03/18/using-time-zone-data-in-sql-server-2008.aspx

    If you are using a previous version of SQL Server I recommend using a composite of DateTime + string which stored the timezone id such as IST, GMT or BST.

    I hope this helps.

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Saturday, March 27, 2010 10:06 AM
  • I would store it in two columns: datetime and string. That way the datetime part is usable. Demo follows.

    SELECT CONVERT(DATETIME,'1977-03-21 22:33:33 IST')
    /*
    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting date and/or time from character string.
    */
    
    SELECT CONVERT(DATETIME,'1977-03-21 22:33:33')
    -- 1977-03-21 22:33:33.000

    Kalman Toth, SQL Server & Business Intelligence Training; http://www.SQLUSA.com
    Saturday, March 27, 2010 2:03 PM

All replies

  • Hi,

    If you are using SQL 2008 you can use the datetimeoffset data type.

    Please find the following link ; - http://blogs.msdn.com/sqlprogrammability/archive/2008/03/18/using-time-zone-data-in-sql-server-2008.aspx

    If you are using a previous version of SQL Server I recommend using a composite of DateTime + string which stored the timezone id such as IST, GMT or BST.

    I hope this helps.

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Saturday, March 27, 2010 10:06 AM
  • hi in simple you mean to say use the column data type as string right?
    Saturday, March 27, 2010 1:45 PM
  • I would store it in two columns: datetime and string. That way the datetime part is usable. Demo follows.

    SELECT CONVERT(DATETIME,'1977-03-21 22:33:33 IST')
    /*
    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting date and/or time from character string.
    */
    
    SELECT CONVERT(DATETIME,'1977-03-21 22:33:33')
    -- 1977-03-21 22:33:33.000

    Kalman Toth, SQL Server & Business Intelligence Training; http://www.SQLUSA.com
    Saturday, March 27, 2010 2:03 PM