I have a really ugly datetime column
defined as datetime2(3)
I want to present it as MM/DD/YYYY
This works when I run it on a variable
select convert(varchar, @MyDate, 32)
But when I run it for the table column it fails:
select convert(varchar, MyDate, 32)
It fails with error
Incorrect syntax near the keyword 'select'
I know this is easy, I'm missing something that should be obvious to me./
You only specify the SELECT keyword once:
convert(varchar, MyDate, 32)
As a bit of a sidebar, I'm surprised that "32" works as a convert "STYLE". It's not listed in the documentation.
That means that MS could change it without any warning. I recommend that you use style "110", instead.
Thank you, @ScottPletcher and @JeffModen.
Jeff I took your suggestion to use the 110 style. The instance is 2019 but the db compatibility level is 2016.
convert(varchar, CustReportDT, 110) as CustReportDT /* MM-DD-YYYY */
Aye. Thank you for the feedback.
Why do you need to convert the date/time to a string? If you just want the date portion - cast it as a DATE data type. And if this is for something else, you really should be using an unambiguous format for that string - either YYYYMMDD or YYYY-MM-DD.
If this data is being exported to SSRS, SSIS or even Excel then you definitely do not want to convert it to a string as that removes any possibility of using those tools to format the dates and times appropriately.