]
07-31-2023, 09:56 AM
There are many formats supported by SQL Server - see the [MSDN Books Online on CAST and CONVERT][1]. Most of those formats are **dependent** on what settings you have - therefore, these settings might work some times - and sometimes not.
The way to solve this is to use the (slightly adapted) **ISO-8601 date format** that is supported by SQL Server - this format works **always** - regardless of your SQL Server language and dateformat settings.
The [ISO-8601 format][2] is supported by SQL Server comes in two flavors:
- `YYYYMMDD` for just dates (no time portion); note here: **no dashes!**, that's very important! `YYYY-MM-DD` is **NOT** independent of the dateformat settings in your SQL Server and will **NOT** work in all situations!
or:
- `YYYY-MM-DDTHH:mm:ss` for dates and times - note here: this format *has* dashes (but they *can* be omitted), and a fixed `T` as delimiter between the date and time portion of your `DATETIME`.
This is valid for SQL Server 2000 and newer.
So in your specific case - use these strings:
insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');
and you should be fine (note: you need to use the international **24-hour** format rather than 12-hour AM/PM format for this).
**Alternatively**: if you're on SQL Server **2008** or newer, you could also use the `DATETIME2` datatype (instead of plain `DATETIME`) and your current `INSERT` would just work without any problems! :-) `DATETIME2` is a lot better and a lot less picky on conversions - and it's the recommend date/time data types for SQL Server 2008 or newer anyway.
SELECT
CAST('02-21-2012 6:10:00 PM' AS DATETIME2), -- works just fine
CAST('01-01-2012 12:00:00 AM' AS DATETIME2) -- works just fine
Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the `YYYYMMDD` format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.
[1]:
The way to solve this is to use the (slightly adapted) **ISO-8601 date format** that is supported by SQL Server - this format works **always** - regardless of your SQL Server language and dateformat settings.
The [ISO-8601 format][2] is supported by SQL Server comes in two flavors:
- `YYYYMMDD` for just dates (no time portion); note here: **no dashes!**, that's very important! `YYYY-MM-DD` is **NOT** independent of the dateformat settings in your SQL Server and will **NOT** work in all situations!
or:
- `YYYY-MM-DDTHH:mm:ss` for dates and times - note here: this format *has* dashes (but they *can* be omitted), and a fixed `T` as delimiter between the date and time portion of your `DATETIME`.
This is valid for SQL Server 2000 and newer.
So in your specific case - use these strings:
insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');
and you should be fine (note: you need to use the international **24-hour** format rather than 12-hour AM/PM format for this).
**Alternatively**: if you're on SQL Server **2008** or newer, you could also use the `DATETIME2` datatype (instead of plain `DATETIME`) and your current `INSERT` would just work without any problems! :-) `DATETIME2` is a lot better and a lot less picky on conversions - and it's the recommend date/time data types for SQL Server 2008 or newer anyway.
SELECT
CAST('02-21-2012 6:10:00 PM' AS DATETIME2), -- works just fine
CAST('01-01-2012 12:00:00 AM' AS DATETIME2) -- works just fine
Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the `YYYYMMDD` format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.
[1]:
[To see links please register here]
[2]:[To see links please register here]