Sean Thorburn February 2016

SQL Server Date CAST default format

Where does SQL Server get the default date format it uses when returning results from:

CAST(LastLogin AS nvarchar(max))

I would like to change this default format. I cannot use CONVERT() because this query is being generated by Entity Framework.

I am getting:

Jun 22 2015  4:02PM
Jul 19 2015 10:02PM
Jan 30 2016  3:45PM
Jan 18 2016  2:56PM
Feb  4 2016 12:27PM
May  7 2015  9:30AM

I have looked at the regional settings on the server and it has not been set to use this date format.

If interested in the EF issue: C# Generic .Contains() method implementing SqlFunctions.StringConvert in Entity Framework


Alexei February 2016

I think one good way is to try to tackle the underlying problem that caused the CAST to be generated. It is better to retrieve DATE/DATETIME/DATETIME2 value into C# DateTime properties and then apply the formatting based on needed format/culture.

[later edit] I think the default string representation of a time depends on both instance language and its default date representation. First can be seen in instance properties:

enter image description here

The second is specified in CAST/CONVERT documentation:

Default for datetime and smalldatetime - mon dd yyyy hh:miAM (or PM)

Both language and datetime format for a session can be changed using SET LANGUAGE and/or SET DATEFORMAT, but the later seems to affect only how datetimes are parsed, not how CAST displays the formatted date.

Heinzi February 2016

I would like to change this default format.

Simply put: You can't.

CAST(LastLogin AS nvarchar(max))

is equivalent to

CONVERT(nvarchar(max), LastLogin)

is equivalent to

CONVERT(nvarchar(max), LastLogin, 0)

which is specified to return the following format:

mon dd yyyy hh:miAM (or PM)

(As other have already noted, this is likely an XY problem, and you might want to start a question on the real problem you are facing.)

Post Status

Asked in February 2016
Viewed 3,838 times
Voted 11
Answered 2 times


Leave an answer