[Solved] SQL Convert Integer to DateTime [closed]

by


Jain, it looks like you are trying to adapt the code in the answer here to your own situation. What Andomar did there was to select the information from a subquery which contained only a single value, since he didn’t have access to the original poster’s source and tables. Doing it that way allows everyone to run and see it, whatever database schema they happen to have.

Adapting something like that to your own code would be very easy: just replace the alias.column reference (here, it was sub.Dt) with your own value, and put your table in the FROM clause instead of the subquery. Thus, if your table was named SomeTable, and your column is named FetchTime, the query becomes:

select  
   convert(datetime, substring(SomeTable.FetchTime,1,4) + '-' + 
                     substring(SomeTable.FetchTime,5,2) + '-' + 
                     substring(SomeTable.FetchTime,7,2) + ' ' +
                     substring(SomeTable.FetchTime,9,2) + ':' + 
                     substring(SomeTable.FetchTime,11,2) + ':' + 
                     substring(SomeTable.FetchTime,13,2))
from      
    SomeTable

You could theoretically have a problem if your FetchTime field was stored as an integer: SUBSTRING() might not work on that data type. To work around that, you could CAST() the value as varchar, like this:

select  
   convert(datetime, substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),1,4) + '-' + 
                     substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),5,2) + '-' + 
                     substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),7,2) + ' ' +
                     substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),9,2) + ':' + 
                     substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),11,2) + ':' + 
                     substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),13,2))
from      
    SomeTable

Or, if you are just after the single value, you could go back to the original query and simply write:

select  
   convert(datetime, substring(sub.Dt,1,4) + '-' + 
                     substring(sub.Dt,5,2) + '-' + 
                     substring(sub.Dt,7,2) + ' ' +
                     substring(sub.Dt,9,2) + ':' + 
                     substring(sub.Dt,11,2) + ':' + 
                     substring(sub.Dt,13,2))
from      
    (select  CAST(FetchTime AS VARCHAR(20)) as Dt FROM SomeTable) sub

1

solved SQL Convert Integer to DateTime [closed]