Home Ask Login Register

Developers Planet

Your answer is one click away!

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

Quote of the day: live life