Post reply

Epoch date and style

  • March 6, 2012 at 4:02 am

    #252036

    Hi all

    I have an epoch date that I have managed to convert into a standard date by doing the following:

    SELECT DATEADD(s, 1074008123, '1970-01-01 00:00:00')

    This returns:

    2004-01-13 15:35:23.000

    However I now want to format the date to be more British. I have tried the following but it is having no impact on the date format, does anyone have any ideas?

    SELECT CONVERT(datetime,DATEADD(s, 1074008123, '1970-01-01 00:00:00'),104)

    Thanks

  • March 6, 2012 at 4:28 am

    #1455191

    The format can be applied to strings only, not to datetime fields.

    Try using CONVERT(varchar(50), someDateTimeExpression, 104).

    Be aware that formatting dates is something that belongs to the presentation layer and should be done on the app side.

    -- Gianluca Sartori

  • March 6, 2012 at 4:33 am

    #1455194

    Hello

    If I do that though it converts it to a varchar where as I want to keep it as a datetime. The reason is that this is to be used as part of a dataset for SSRS report.

    Thanks

  • March 6, 2012 at 4:44 am

    #1455197

    The important thing is that the datetime column stores the right value.

    To change the way it displays on the report, try changing the report field.

    -- Gianluca Sartori

  • March 6, 2012 at 4:49 am

    #1455203

    Gianluca Sartori (3/6/2012)

    The important thing is that the datetime column stores the right value.

    To change the way it displays on the report, try changing the report field.

    i.e. assuming the name of the column in your table is "datecol": instead of putting "datecol" in the report, put something like "CONVERT(varchar(36), datecol, 104)".


    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]

    If you don't have time to do it right, when will you have time to do it over?

  • March 6, 2012 at 5:20 am

    #1455218

    Gianluca Sartori (3/6/2012)

    The important thing is that the datetime column stores the right value.

    To change the way it displays on the report, try changing the report field.

    Hi yes, it dawned on me that would be the better thing to do.

    Thanks for your help.

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

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