Convert Date Field - RESOLVED

Transact-SQL
1

Experts,
I have a really ugly datetime column
defined as datetime2(3)
2020-11-16 14:38:07.1970000

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 Column2,
select convert(varchar, MyDate, 32)
FROM dbo.MyTable

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./
Thanks

2

You only specify the SELECT keyword once:


SELECT Column2,
    convert(varchar, MyDate, 32)
FROM dbo.MyTable
2 Likes
3

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.

2 Likes
4

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 */

Thank you.

5

@DBAforever ,

Aye. Thank you for the feedback.

6

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.