CODEWITHSUNDEEP
sqlsql-servert-sql
ff0000e2
ff0000e2

Reputation: 437

SQL Server Returning Strange Dates using CONVERT()

CONVERT(VARCHAR(11),Enrollment.[Enroll End],106)

I thought the above was supposed to return a date like '12 Jan 2016', it is instead returning dates like '15 Oca 2016'. Does anyone know why this is happening? I originally thought maybe the language was set incorrectly but it was not.

https://msdn.microsoft.com/en-us/library/ms187928.aspx denotes 106 with this

1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

I'm not sure if the above has something to do with it - though I am not entirely sure what they mean by nondeterministic results

Thanks for the help!

Upvotes: 3

Views: 68

Answers (2)

user3583912
user3583912

Reputation: 1322

I hope this helps.. if local setting are different. always use dateformat

set dateformat dmy

declare @date datetime = '12-01-2016'

select CONVERT(VARCHAR(11),@date,106)

Upvotes: 0

Tom H
Tom H

Reputation: 47444

The values returned are non-deterministic because they rely on the locale settings of the user connection. In other words, you can run the same exact code from two different computers and get different results - you can't determine the result based solely on the parameters.

Make sure that your client machine is set to an English locale.

Upvotes: 2

Related Questions