SQL Server Date CAST default format

Question

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
etc.

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

Why are you converting to a string anyways if you're using EF? Why not just do that on the client side? — D Stanley, Feb 08 '16 at 20:55
Can't you tell entity framework to return the value as a `DateTime`? Then you can format the date as you wish using .NET formatting options. — Heinzi, Feb 08 '16 at 20:55
Don't use sql-server, but most DBMS platforms have some kind of FORMAT function, where you can specify the format to use to translate date/time fields to a string. — DBug, Feb 08 '16 at 20:55
@DStanley SET DateFormat has no affect on the CAST result. I am getting this result in SQL. — Sean Thorburn, Feb 08 '16 at 21:05
@Heinzi, Sure, but that's another problem. This question is MSSQL based. — Sean Thorburn, Feb 08 '16 at 21:08

Alexei - check Codidact · Answer 1 · 2016-02-09T06:10:36.810

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:

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.

Thanks Alexi, that will be the next step. This question is simply - Where is SQL server getting this default Date format from. — Sean Thorburn, Feb 08 '16 at 21:07

score 2 · Accepted Answer · edited Mar 20 '17 at 10:29

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.)

vbif · Answer 3 · 2023-08-18T11:28:28.957

You can use Format function instead of cast. Result for British English is like:

select
  format(getdate(),'F'), -- 18 August 2023 14:22:39
  format(getdate(),'f'), -- 18 August 2023 14:22
  format(getdate(),'G'), -- 18/08/2023 14:22:39
  format(getdate(),'g'), -- 18/08/2023 14:22
  format(getdate(),'D'), -- 18 August 2023
  format(getdate(),'d'), -- 18/08/2023
  format(getdate(),'T'), -- 14:22:39
  format(getdate(),'t')  -- 14:22

SQL Server Date CAST default format

3 Answers3