How to Convert a Date from a Character String

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

Convert character string into this specific date format?

so far your sample text is a valid datetime in mssql when I tried to cast. It seems there's some invalid data on your table. try using try_cast() to include those invalid data.

declare @ReviewDate varchar(max)='Mr John wrote a review in Oct 2017'

set @ReviewDate = (RIGHT(@ReviewDate, 8))

select try_cast(@ReviewDate as datetime) as [ReviewDate2]

dbfiddle<>

Convert a large character string to dates, but the dates have a non numeric character

You can use :

dates_MODIS <- c("X2015.01.01", "X2015.01.17", "X2015.02.02")
dates_MODIS <- as.Date(dates_MODIS, 'X%Y.%m.%d')
dates_MODIS
#[1] "2015-01-01" "2015-01-17" "2015-02-02"

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

How to convert a date from a character string?

If we need the 'date' and 'time', one option is as.POSIXct

 as.POSIXct(date, format='%m-%d-%Y-%I:%M%p')
#[1] "2015-07-21 09:30:00 EDT"

How to convert character string with as.Date but display as dd/mm/yyyy

One way to achieve what you want is to create your own S3 class. This isn't as difficult as it sounds, requiring only two lines of code:

as.myDate <- function(x, ...) `class<-`(as.Date(x, ...), c("myDate", "Date"))
format.myDate <- function(x, ...) format(`class<-`(x, "Date"), "%d/%m/%Y")

This then allows you to do:

p$date <- as.myDate(p$mr_daterd, format = "%d/%m/%Y")

p
#> mr_daterd date
#> 1 18/03/2011 18/03/2011
#> 2 10/01/2009 10/01/2009
#> 3 10/01/2009 10/01/2009
#> 4 11/03/2004 11/03/2004
#> 5 10/01/2009 10/01/2009
#> 6 18/03/2011 18/03/2011
#> 7 16/09/2005 16/09/2005
#> 8 10/01/2009 10/01/2009
#> 9 18/03/2011 18/03/2011
#> 10 10/01/2009 10/01/2009
#> 11 18/03/2011 18/03/2011
#> 12 18/03/2011 18/03/2011
#> 13 28/03/2013 28/03/2013
#> 14 28/03/2013 28/03/2013
#> 15 10/01/2009 10/01/2009

This new class inherits all other generic functions from "Date" so will still behave the same as a Date object:

difftime(p$date[2], p$date[1])
#> Time difference of -797 days

plot(p$date, rnorm(15))

Sample Image

Change date format for char data type

The key problem is that you are storing your date value as a string. You should never do that because it will almost always result in problems further down the line.

Therefore to change the formatting you first have to convert your current string into a valid date and then you use convert to format it as you desire.

SELECT [Date]
-- First convert to a valid date time - the current format needs to be modified
, CONVERT(DATE, SUBSTRING([DATE], 1, 2) + '-' + SUBSTRING([DATE], 3, 2) + '-' + SUBSTRING([DATE], 5, 4), 105) [Proper Date Value]
-- Then convert back to a string in the desired format
, CONVERT(VARCHAR(8), CONVERT(DATETIME, SUBSTRING([DATE], 1, 2) + '-' + SUBSTRING([DATE], 3, 2) + '-' + SUBSTRING([DATE], 5, 4), 105), 112) [Formatted Date Value]
-- In fact you can actually just use direct string manipulation in this case
, SUBSTRING([DATE], 5, 4) + SUBSTRING([DATE], 3, 2) + SUBSTRING([DATE], 1, 2)
FROM (
VALUES
('10112021'),
('11112021'),
('12112021')
) AS D ([Date]);

Returns:



Leave a reply



Submit