Post reply

time_t and to_date conversions

  • November 5, 2002 at 12:35 pm

    #357648

    I have a character field which has a time_t date (time in seconds) that I need to convert. In Sqlplus I would do it with to_date - how can I do this in T-SQL. What does to_date functions in SQLSERVER?

    thanks- steven

    sjackson@hq.dcma.mil

  • November 5, 2002 at 1:03 pm

    #440378

    I am giving the code used in Oracle and SQL Server for converting the character value to date.

    Oracle:

    SELECT TO_DATE(

    '15.3.2000 23:56:33',

    'DD.MM.YYYY hh24:mi:ss',

    'NLS_DATE_LANGUAGE = German')

    INTO DiffDate FROM DUAL;

    SQL Server:

    DECLARE @Date varchar(50)

    SELECT @Date = '15.3.2000 23:56:33'

    Print CONVERT(DATETIME,@Date,104)

    Hope this is what you are looking for.

  • November 5, 2002 at 1:16 pm

    #440381

    Thanks so much for your reply. I'm new to SQLSERVER so bare with me. I'm selecting a field from a table (opendate)that is a character field in time_t. If I do

    select convert(DATETIME,opendate/86400)from tablename - I don't get a valid answer. What am I missing? Thanks Steve

  • November 5, 2002 at 1:30 pm

    #440386

    The syntax for CONVERT is:

    CONVERT(datatype, value or field, style)

    so...

    CONVERT(datetime, time_t, 1) in pseudocode is

    convert the information in time_t to datetime using the mm/dd/yy style.

    See the Books Online, go to the Index and look under CONVERT for the full list of styles.

    -Bill

  • November 5, 2002 at 2:13 pm

    #440397

    You stated it is the time in seconds. I am thinking most likely seconds since midnight 1/1/1970 (GMT or not? as you will need to adjust to local time).

    SQL Server

    SELECT DATEADD(s,time_t,'1/1/1970')

  • November 5, 2002 at 2:38 pm

    #440405

    Thanks! This worked.

    SELECT DATEADD(s, open_date, '1 / 1 / 1970') AS conv_dt FROM table_name

    Much appreciated

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

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