CODEWITHSUNDEEP
sql-server
Ben
Ben

Reputation: 609

QUOTENAME alters date format

I've got a datetime field in my table which shows dates like this:

2017-01-18 10:20:19.617

I'm using QUOTENAME to output the table with the fields wrapped in quotes so it will be compatible to import into a legacy CSV based app.

SELECT QUOTENAME(P.CreatedOn,'"') as CreatedOn

That changes the output to

"Jan 18 2017 10:20AM"

Ideally I need

"2017-01-18 10:20:19.617"

Is that possible?

Upvotes: 3

Views: 1327

Answers (2)

Disillusioned
Disillusioned

Reputation: 14832

This should do the trick. The CONVERT function allows you to explicitly control the format of your date conversion.

declare @d datetime = '20170119 20:21:22'
select QUOTENAME(CONVERT(varchar(23), @d, 121), '"')

Some guidance notes:

  • The style parameter 121 indicates the date/time must be formatted in the ODBC format which is yyyy-mm-dd HH:MM:ss[.ttt[tttt]][{+|-}offset]. It always includes the fractions of a second at 3 or 7 digits depending on the date type of the value to be converted.
  • This supports all datatypes combining date & time: datetime, datetime2 (7 digit fraction of a second), smalldatetime, datetimeoffset
  • date and time datatypes exclude time a date portions from the string respectively.
  • Refer to the linked page to experiment with other conversion styles. E.g. style 120 is similar, but excludes the optional fractions of a second from the format.
  • Note that if the the varchar(...) target type is to short to hold the full string, it will be truncated from the right.

Upvotes: 4

Stoleg
Stoleg

Reputation: 9320

QUOTENAME returns nvarchar (258). When you are supplying DATE to the function it does conversion to nvarchar.

If you convert to varchar or nvarchar first (as @CraigYoung suggesting), then this function will not do conversion itself and will not change the string.

In CONVERT you can specify any format you want for your date to be in using list available formats.

Upvotes: 0

Related Questions