Converting varchar to date time error

Transact-SQL
1

My source table is varchar

Date
20090225121337
20030413082345
20070907030987

I need to convert the entire column to date time

Date
2009-02-25 12:13:37
2003-04-13 08:23:45

Using cast(left(date,14) as datetime) as date. Gives error.
Conversion failed converting varchar to date and or time.

2

Try this: Select cast(stuff(stuff(stuff(date, 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') As datetime)

3

Here's a way that's much easier to read without the deeply nested set of calls, and therefore perhaps easier to maintain (which is what I always shoot for first). I have no idea how its performance would compare. You would not actually want to declare @InputDate like I did, which is just for the example.

DECLARE @InputDate	AS VARCHAR (14) = '20090225121337';
DECLARE @Year		AS VARCHAR (4) = SUBSTRING(@InputDate, 1, 4);
DECLARE @Month		AS VARCHAR (2) = SUBSTRING(@InputDate, 5, 2);
DECLARE @Day		AS VARCHAR (2) = SUBSTRING(@InputDate, 7, 2);
DECLARE @Hour		AS VARCHAR (2) = SUBSTRING(@InputDate, 9, 2);
DECLARE @Minute		AS VARCHAR (2) = SUBSTRING(@InputDate, 11, 2);
DECLARE @Second		AS VARCHAR (2) = SUBSTRING(@InputDate, 13, 2);
SELECT DATETIMEFROMPARTS(@Year, @Month, @Day, @Hour, @Minute, @Second, 0);

Both our methods produce the seconds to thousandths, which is not in the original example.

4

Houston, we have a problem with 20070907030987

if OBJECT_ID('tempdb..#dstyles') is not null
	drop table #dstyles
	go


create table #dstyles(funkydates varchar(150))

insert into #dstyles
select '20090225121337' union
select '20030413082345' union
select '20070907030987' 

select DATETIMEFROMPARTS(_year, _month,_day,_hour,_minute,_second, _millivanilly)
  from (
  select SUBSTRING(funkydates, 1, 4) as _year, 
         SUBSTRING(funkydates, 5, 2) as _month,
		 SUBSTRING(funkydates, 7, 2) as _day,
		 SUBSTRING(funkydates, 9, 2) as _hour,
		 SUBSTRING(funkydates, 11, 2) as _minute,
		 SUBSTRING(funkydates, 13, 2) as _second,
		 0 _millivanilly
		 from #dstyles
) _slimx

Select cast(stuff(stuff(stuff(funkydates, 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') As datetime)
  from #dstyles

This does not work on some of those dates, I think it is the odd hours part that starts with 0

Cannot construct data type datetime, some of the arguments have values which are not valid.

1 Like
5

This works because the seconds were 87, no such thing unless you do some conversion, subtraction to get the milliseconds and stuff

select DATETIMEFROMPARTS(_year, _month,_day,_hour,_minute,_second, _millivanilly)
  from (

    select SUBSTRING(funkydates, 1, 4)  as _year, 
         SUBSTRING(funkydates, 5, 2) as _month,
		 SUBSTRING(funkydates, 7, 2) as _day,
		 SUBSTRING(funkydates, 9, 2) as _hour,
		 SUBSTRING(funkydates, 11, 2) as _minute,
		 case 
			when cast(SUBSTRING(funkydates, 13, 2) as int) > 60 then 59.99
			else cast(SUBSTRING(funkydates, 13, 2) as int)
			end as _second,
		 0 _millivanilly
		 from #dstyles
) _slimx
6

How can a apply any of these logic to a a column that I already brought over to anew Table . When I do a alter table alter column to datetime it still says error converting varchar to datetime

In my original import query I have
Select Id,
User,
State,
Convert(datetime,Created_on) as date ——this is where the error appears (example of 2007052110050 dates have 14 characters)
From table

7

That's what they are telling you. You cant' run a simple convert like that because the db doesn't know the format. If you run a simple convert like you did on the variable below, you will get same error, but if you manipulate it a little to match a known format it will work. Yosiaz pointed out that if the data isn't consistent, even the second query will fail. For instance, if the 0 is missing in front of the 5, then it will try to turn 52 into a month, which is invalid

declare @s varchar(50) = '2007052110050'

select Convert(datetime, @s) -- fails
select Convert(datetime, substring(@s,1,8) + ' ' + substring(@s,9,2) + ':' + substring(@s,11,2) + ':' + substring(@s,13,2) , 120)

2 Likes
8

Look at the following result. the try_ columns are null. indicating it is not a valid

create table #dstyles(columnmigrate varchar(150))

insert into #dstyles
select '20090225121337' union
select '20030413082345' union
select '20070907030987'


--TRY_CAST -> Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
Select * , 
   TRY_CAST(columnmigrate as date ), 
   TRY_CAST(columnmigrate as time),
   TRY_CAST(columnmigrate as datetime),
   TRY_CAST(columnmigrate as datetime2),
   TRY_CAST(columnmigrate as datetimeoffset),   
   TRY_CAST(columnmigrate as smalldatetime)
from #dstyles 
--where isdate(columnmigrate) = 1


drop table #dstyles
2 Likes
9

This doesn’t work

10

What doesn't work?