Date conversion

  • Morning,

    I am trying to write a script which will display a date as dd-mm-yyyy hh:mm:ss. Currently the date is stored like '2021-12-10 20:18:53.447' when i run the script below it returns '10/12/2021 08:18:53'

    SELECT 
    format(Datefield,'dd/MM/yyyy hh:mm:ss') AS 'DateFormatted'
    FROM
    Table1

    The day, month and year part are showing correctly however for some reason the hour in the time part is showing incorrectly and displays 08 instead of 20. Does anyone know how i can show the time part correctly?

    Thank you in advance

  • Change hh to HH

    Also if you actually want your format, change your "/"s to "-"s

  • Thank you, that's fixed it

  • Please read this article. The FORMAT() function performs very badly.

    • This reply was modified 2 months, 1 week ago by  Phil Parkin. Reason: Fix typo

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin wrote:

    Please read this article. The FORMAT() function performs very badly.

    Agreed, I was just tidying up their code. An alternative:

    SELECT 
    CONVERT(VARCHAR(25),Datefield,105)+' '+CONVERT(VARCHAR(25),Datefield,108)
    FROM
    Table1

    • This reply was modified 2 months ago by  MarkP. Reason: Code formatting
  • The question not asked is why do you want to do this?  What application is going to consume this data - is it for a report, exporting to Excel, exporting to CSV - something else?

    In almost all cases, outputting date/time data in a string format is a bad idea.  The consuming application loses the ability to work with that data as a date/time.  For example, a web page cannot localize a string - but it can localize a date/time.  SSRS can be set to format the data in various formats including localization.

    Copy/Paste to Excel - string format doesn't really matter (except for milliseconds - which cause a 'display' only issue). CSV files should always be output in an unambiguous format, which the selected format is definitely not.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams - to answer your questions its a query which is getting pulled into tableau and the tableau analysts i work with asked for it to be corrected which is why i asked because i didn't understand why the time and date was different on my format string however MarkP seemed to solve my problem because as soon as i changed the the hh to HH it worked.

    Also thank you MarkP and Phil Parkin i have a read of your article

  • JSEYMOUR50@YMAIL.COM wrote:

    Jeffrey Williams - to answer your questions its a query which is getting pulled into tableau and the tableau analysts i work with asked for it to be corrected which is why i asked because i didn't understand why the time and date was different on my format string however MarkP seemed to solve my problem because as soon as i changed the the hh to HH it worked.

    Also thank you MarkP and Phil Parkin i have a read of your article

    This goes back to my point - by converting the date/time to a string the query pulling the data has no idea that the values in that column are dates and times.  Therefore, they have to convert the data in Tableau to a valid date/time after importing the data into their system.  If the column was just returned as a standard date/time column that wouldn't be necessary and there wouldn't be a need to format a string.

    If the process is actually exporting the data to a file before loading it into Tableau - then using an ambiguous format is not the correct way to send that data either.

    I find it amazing how much extra time and effort is spent on providing bad data - leading to queries that take much longer than is necessary because of all the converting/formatting of data that just isn't necessary.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 8 posts - 1 through 7 (of 7 total)

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