Use CONVERT(datetime, '2025-10-08 00:00:00.000', 120) instead of CAST.
Note the style is 120, which indicates the string is in ODBC-canonical format, which is extremely close to the ISO8601 standard format.
Note, for deterministic translations of date and time values independent of resource locales, Operating System settings, client settings, or SQL Server settings, use the following conversion:
Never use cast, it is the devil spawn. Ok, use cast if you're not using SQL Server, or need standards compliance, but really really as a professional DBA, please use convert, and always with a format specifier (the 120 piece).
You're correct, but that also doesn't change that I'm with the OP in being disappointed that the unambiguous input is mutilated if you don't specify a format.
You'd need the datetime value to be already in UTC for that since the "Z" indicates zulu time
Technically speaking, type 120 indicates that the datetime value is in ODBC-canonical format, which doesn't require the T between the date and time components, and is without a timezone specified, hence no Z at the end.
Are you possible using “British English” in your SQL Server instance or db? I found some results online that indicate that’s the expected behavior when the language is set to that.
There used to be only 3 unambiguous formats for specifying datetime in SQL Server - `yyyymmdd`, `yyyy-mm-ddThh:mm:ss` and `yyyy-mm-ddThh:mm:ss.mil`. I'd have sworn they got rid of some of the roughness in about the 2008/2012 product timeframe but if you stick to those formats **if you have to use strings at all** then you should be golden.
I've seen this before, it reads the 4 digit as the year and then the 10 08 by the DMY rule: 10 August, giving the result: 2025-08-10. Setting the format to ymd might resolve this?
Ugh. Yesterday I was trying to work out how to use the current date in a SQL query on sql server but because I'm a bit of a noob I didnt know that sql server is "special" and has to specify it in its own way, so I was so confused when the recommended methods didnt work.
Have I seen Oracle's "HH24 means hour of day and HH means of day in 12h format" ?
Or "ss means second in minute and sssss means second in day"
Or "MM and mm are the same, use MI for minutes"
Use
CONVERT(datetime, '2025-10-08 00:00:00.000', 120)instead of CAST.Note the style is 120, which indicates the string is in ODBC-canonical format, which is extremely close to the ISO8601 standard format.
Note, for deterministic translations of date and time values independent of resource locales, Operating System settings, client settings, or SQL Server settings, use the following conversion:
CONVERT(datetime, '2025-10-08T00:00:00.000', 126);See the Microsoft documentation for CAST and CONVERT for all the details.
Yeah I was just trying to filter some records quick and dirty with
WHERE CreationTime >= '2025-10-08'and noticed the discrepancyNever use cast, it is the devil spawn. Ok, use cast if you're not using SQL Server, or need standards compliance, but really really as a professional DBA, please use convert, and always with a format specifier (the 120 piece).
You're correct, but that also doesn't change that I'm with the OP in being disappointed that the unambiguous input is mutilated if you don't specify a format.
The format is implicitly specified in the default language/region of the database
Although it never fails if it's a proper ISO8601, like '2025-10-08T00:00:00', even witouth CAST
Why someone write a query with a hardcoded "2025-10-08" ???????
I wouldn't call it "hardcoded". I was testing something and didn't want to scroll down every time so I limited the records to that day.
versus 119 or 121?
Microsoft missed an opportunity to go with 42 as the style, which would have been much better in my opinion.
It's weird that that works because that string is not ISO 8601.
I suppose to be entirely compliant with ISO8601 you could use
CONVERT(datetime, '2025-10-22T16:43:22.123', 126);Type 127 could be used instead of 126 if your data includes a time-zone component, ala:
CONVERT(datetime, '2025-10-22T16:43:22.123Z', 127);You'd need the datetime value to be already in UTC for that since the "Z" indicates zulu time
Technically speaking, type 120 indicates that the datetime value is in ODBC-canonical format, which doesn't require the
Tbetween the date and time components, and is without a timezone specified, hence noZat the end.[deleted]
Thanks for the explanation! It's not the date format of the operating system so it must be set in the server/database somewhere.
If you don't set it manually, it's inherited from your system locale.
Yeah that's not normal. Your SSMS is sick, poor thing 😔
Are you possible using “British English” in your SQL Server instance or db? I found some results online that indicate that’s the expected behavior when the language is set to that.
``` Select @@language
```
I was about to comment just "LOCALE!!!" but you've put it much more coherently 🤭
How is that even possible??? I thought that ymd is the only reliable way to fix american date errors
Because Sql server just sucks
MySQL users: First time?
There used to be only 3 unambiguous formats for specifying datetime in SQL Server - `yyyymmdd`, `yyyy-mm-ddThh:mm:ss` and `yyyy-mm-ddThh:mm:ss.mil`. I'd have sworn they got rid of some of the roughness in about the 2008/2012 product timeframe but if you stick to those formats **if you have to use strings at all** then you should be golden.
Is it a Microsoft DB? If so I'm not surprised.
ISO8610 strikes again!
I got the expected result for some reason.
Must be a database setting then.
If you run “DBCC USEROPTIONS” what do you see for dateformat? For me it’s “mdy”. If I change with “set dateformat dmy” the cast shows your result.
I'll check when I get a chance
I've seen this before, it reads the 4 digit as the year and then the 10 08 by the DMY rule: 10 August, giving the result: 2025-08-10. Setting the format to ymd might resolve this?
Ugh. Yesterday I was trying to work out how to use the current date in a SQL query on sql server but because I'm a bit of a noob I didnt know that sql server is "special" and has to specify it in its own way, so I was so confused when the recommended methods didnt work.
I really wish they would let you use Floor(GetDate()), that would be simple and expressive.
Have you even seen Oracle's default NLS_DATE_FORMAT?
MsSQL is pretty good with dates, in that it's forgiving on input and really flexible on output.
Have I seen Oracle's "HH24 means hour of day and HH means of day in 12h format" ? Or "ss means second in minute and sssss means second in day" Or "MM and mm are the same, use MI for minutes"
It's hell.
That's user config, when you create a user you specify the locale to use for displaying data, you can override it with a format string.