none
Data Type Conversion RRS feed

  • Question

  • Hi all,

      I am working in a database thats runs lively..

    There  is a column named timecreated with the datatype bigint.And also there are several datas stored.The main thing is the date value is converted into unix time and stored in column.

    I knew there is no option to convert column datatype bigint to datetime ,if the column having existing datas.

    so post answer if there is any method to do this...or any other way?

    Tuesday, September 24, 2013 4:40 AM

Answers

  • Try the below:

    SELECT DATEADD(SS, 1379998561, '01/01/1970')


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Mohan DBA Tuesday, September 24, 2013 6:07 AM
    Tuesday, September 24, 2013 5:30 AM
  • Thast because, Unix epoc is the time 00:00:00 UTC on 1 January 1970 (or 1970-01-01T00:00:00Z ISO 8601).

    Ref: http://en.wikipedia.org/wiki/Unix_time


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Mohan DBA Tuesday, September 24, 2013 8:54 AM
    Tuesday, September 24, 2013 8:50 AM
  • Once you created a new datetime column in your table, then you can use the below query to update the newcolumn values:(This is for existing value. For new value, as you said, you can do this format at your presentation layer.)

    update Set NewCol = DATEADD(SS, OldCol, '01/01/1970')
    From TableName

    Note: Please do it at downtime to make a smooth transfer and to avoid locking issues.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Mohan DBA Tuesday, September 24, 2013 6:19 AM
    Tuesday, September 24, 2013 6:11 AM
  • Thanks Yaar,

    I got it...But the query like

    update TableName Set NewCol = DATEADD(SS, OldCol, '01/01/1970')
    

    • Marked as answer by Mohan DBA Tuesday, September 24, 2013 6:20 AM
    Tuesday, September 24, 2013 6:20 AM

All replies

  • Could you please show us some sample data and desired output form?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, September 24, 2013 4:47 AM
  • So I assume there are some existing data with bigint and you want that to be bigint.

    Here are the steps that I do normally

    1. Add a new column with proper (datetime here ) data type
    2. Convert all the existing data (bigint->datetime) ad update the new column. You may have to do this in a loop for a set of records per trasaction(may be 5000) to avoid lock escalation.
    3.Once you update all the rows, drop the *old* column and rename the *new column*
    Dont forget to validate the constraints if any on that column and apply the same on teh new columns as well


    Satheesh
    My Blog



    Tuesday, September 24, 2013 4:55 AM
  • Read

    Cast and Convert


    Many Thanks & Best Regards, Hua Min

    Tuesday, September 24, 2013 4:55 AM
  • int the image i  marked the field..the original data is 2013/09/24 10:26:10. it stored as 1379998561.

    I am going to ask the program developer to remove the unix time conversion progress.

    Now i want to convert the existing datas to original time  and also convert the column datatype....

    Tuesday, September 24, 2013 5:08 AM
  • Try the below:

    SELECT DATEADD(SS, 1379998561, '01/01/1970')


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Mohan DBA Tuesday, September 24, 2013 6:07 AM
    Tuesday, September 24, 2013 5:30 AM
  • Do you just want to show the converted values in some reports or actually update the base table with new data type?

    If you want to update the base table You can follow the steps that I posted earlier

    Apart from that you could try adding computed columns also

     

    Satheesh
    My Blog


    Tuesday, September 24, 2013 5:48 AM
  • hi,

    the query is fine...

    but i don't know how to copy the existing column values to new column in single update query

    Tuesday, September 24, 2013 6:07 AM
  • Once you created a new datetime column in your table, then you can use the below query to update the newcolumn values:(This is for existing value. For new value, as you said, you can do this format at your presentation layer.)

    update Set NewCol = DATEADD(SS, OldCol, '01/01/1970')
    From TableName

    Note: Please do it at downtime to make a smooth transfer and to avoid locking issues.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Mohan DBA Tuesday, September 24, 2013 6:19 AM
    Tuesday, September 24, 2013 6:11 AM
  • If you are going to do this on a *live* database( and a huge table) there are chances that lock escalation can happen and table will be in-accessible till the update is over. SO just be aware of Lock escalation

    Satheesh
    My Blog


    Tuesday, September 24, 2013 6:15 AM
  • Thanks Yaar,

    I got it...But the query like

    update TableName Set NewCol = DATEADD(SS, OldCol, '01/01/1970')
    

    • Marked as answer by Mohan DBA Tuesday, September 24, 2013 6:20 AM
    Tuesday, September 24, 2013 6:20 AM
  • No issues, its just a variant.

    As long as you are going to do for entire table (assuming no NULL values and proper integer values), that shouls be fine.

    Otherwise, it should be:

    update Set NewCol = DATEADD(SS, OldCol, '01/01/1970')
    From TableName Where OldCol is not null


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, September 24, 2013 6:31 AM
  • hi,

    can i know why you mention the date '01/01/1970' in above post.

    And is there any logic behind that?

    Tuesday, September 24, 2013 8:40 AM
  • Thast because, Unix epoc is the time 00:00:00 UTC on 1 January 1970 (or 1970-01-01T00:00:00Z ISO 8601).

    Ref: http://en.wikipedia.org/wiki/Unix_time


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Mohan DBA Tuesday, September 24, 2013 8:54 AM
    Tuesday, September 24, 2013 8:50 AM