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
September 26, 2022 at 8:43 am
Thank you, that's fixed it
September 26, 2022 at 10:27 am
Please read this article. The FORMAT() function performs very badly.
September 26, 2022 at 10:38 am
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
September 26, 2022 at 7:58 pm
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
September 27, 2022 at 6:46 am
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
September 27, 2022 at 3:18 pm
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