CODEWITHSUNDEEP
sqlsql-server
RJ10
RJ10

Reputation: 303

Convert datetime format in sql

I'm trying to convert a datetime format,convert function have not returned correct value.

here is my data

2015-09-07 07:00:01.0730000 +08:00

my query 

 select convert(varchar(16), '2015-09-07 07:00:01.0730000 +08:00', 103)

but it returns

2015-09-07 07:00

Expected result

07/09/2015 07:00

Upvotes: 1

Views: 1390

Answers (2)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

select convert(varchar(100),cast('2015-09-07 07:00:01.0730000 +08:00' as datetime),103)
        +' '+
       SUBSTRING('2015-09-07 07:00:01.0730000 +08:00',12,5)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The mystery is explained in the documentation:

When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. . Beginning with SQL Server 2012, the only styles that are supported when converting from date and time types to datetimeoffset are 0 or 1. All other conversion styles return error 9809.

That is, the style is based on the expression, not on the target data type. I find this a bit counter-intuitive. But, it means that the 103 is being ignored, because the argument is a string and not a date/time data type.

Hence, you are just converting a string to a string, so nothing happens. This is perhaps clearer with this example:

select convert(varchar(255), 'hello world', 103)

This has nothing to do with a datetime and it returns 'hello world' (see here).

I find this curious behavior, but it is documented so the behavior is correct.

Upvotes: 2

Related Questions