Conversion failed when converting date and/or time from character string while inserting datetime
There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. 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 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 fixedT
as delimiter between the date and time portion of yourDATETIME
.
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.
SQL Error : Conversion failed when converting date and/or time from character string
I found the issue, it's a very very stupid mistake, there was one column with a text format I couldn't see it because I checked with length, but the SQL gives max length so I used group by and manually check dates until I found one column that's not correct in 1 m account records
SQL - Conversion failed when converting date and/or time from character string
What you can do is update the value using try_convert()
first and then alter the column name. Note: This will set any invalid values to NULL
.
update customer
set dob = try_convert(date, dob);
alter table customer alter column dbo date;
If you want to see the bad values, then before you change the table, run:
select c.*
from customer c
where try_convert(date, dob) is null and dob is not null;
You may have other ideas on how to fix the values.
T SQL Conversion failed when converting date and/or time from character string from VARCHAR(MAX)
I have a date stored as
varchar(MAX)
There's your problem right there.
Not only you are using the wrong data type to store dates, you are also using max
which is a known performance killer.
The solution to the problem is to alter the table and store dates in a Date
data type - but first, you must look up all the objects that depends on that column and make sure they will not break or change them as well.
Assuming this can't be done, or as a temporary workaround, you must first convert the data you have to Date
, and then convert it back to a string representation of that date using the 103
style to get dd/mm/yyyy
.
Since yyyy-mm-dd
string format is not culture dependent with the date data type, you can simply do this:
SELECT CONVERT(char(10), TRY_CAST(DateField As Date), 103) As [JobStartDate]
FROM tblTest
Note I've used try_cast
and not cast
since the database can't stop you from storing values that can't be converted to dates in that column.
Datetime! Conversion failed when converting date and/or time from character string
You can try this:
select
try_convert(datetime2(7),
STUFF(STUFF(REPLACE('2019-02-18-13.10.46.485000015','.',':'), 11, 1, ' '),20,1,'.')
,121)
Conversion failed when converting date and/or time from character string when selecting in CTE
You can try using some default date value instead of '0'
CASE WHEN j.PHPPDT = '00010101' THEN '12/31/2099' ELSE j.PHPPDT END AS NEWPPDT,
CASE WHEN j.PHPDTE = '00010101' THEN '12/31/2099' ELSE j.PHPDTE END AS NEWPROMDT
Getting Conversion failed error when converting date and/or time from character string
Storing datetime in varchar column is bad idea.Use appropriate column type when storing data.
Considering your question i assume there is invalid characters included in data like CR, LF or CR+LF.
try to replace them by
SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')
Related Topics
What the Difference Between MySQL and MySQL2 Gem
How to Obtain a Query Execution Plan in SQL Server
Delete All Duplicate Rows Except For One in MySQL
Search All Fields in All Tables For a Specific Value (Oracle)
Cross Join VS Inner Join in Sql
Is There a Combination of "Like" and "In" in Sql
Dynamic Alternative to Pivot With Case and Group By
MySQL, Update Multiple Tables With One Query
MySQL - How to Unpivot Columns to Rows
SQL Counting All Rows Instead of Counting Individual Rows
Equivalent of Explode() to Work With Strings in MySQL
Difference Between Oracle'S Plus (+) Notation and Ansi Join Notation
Grouped Limit in Postgresql: Show the First N Rows For Each Group