Converting date and/or time from character string.

Question

Converting date and/or time from character string.

hb21l6

SSCommitted

Points: 1647
More actions
December 13, 2016 at 3:52 am

#332705

Hi all,
I have a basic problem (I hope) whereas I'm trying to insert 4m rows from one table to another, but I'm struggling with dates.
The table the data is coming from are all nvarchar fields, and the target is a datetime. So, I'm having to put the date (dd/mm/yy) into the correct format as shown below.
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
Now, I've tried to only import where the field is actually recognised as a date by using the "isDate" function (see below), but even with this, I still get the same error.
if I execute the 'select' part of the statement, 1000 rows return without error. its just when I'm trying to insert them.
insert into tbl_InvoiceHead
([Invoice_Number],[Invoice_Date],[Call_ETA_Date])
select top 1000
[invoice_number],
convert(datetime,'20'+right(invoice_date,2)+'-'+ right(left(invoice_date,5),2)+'-'+left(invoice_date,2) ,102) as invoice_date
,convert(datetime,'20'+right(call_eta_date,2)+'-'+ right(left(call_eta_date,5),2)+'-'+left(call_eta_date,2) ,102) as call_eta_date
FROM tbl_OldHeadTable
where invoice_number not in (select distinct invoice_number from tbl_InvoiceHead)
and isdate(convert(datetime,'20'+right(invoice_date,2)+'-'+right(left(invoice_date,5),2)+'-'+left(invoice_date,2) ,102)) =1
and isdate(convert(datetime,'20'+right(call_eta_date,2)+'-'+right(left(call_eta_date,5),2)+'-'+left(call_eta_date,2) ,102)) =1
On the destination table, there is one other datetime field, which has getdate() as default. This is called insertedDate.
Any pointers would be much appreciated.
Thanks in advance.
Dave

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

Can you supply some sample data and DDL (see the link in my signature). Without knowing what your data, especially the date field, looks like, this is going to be difficult to troubleshoot.

Edit: on a side note, perhaps avoid using dd/mm/yyyy format. If your login is set to American, then it'll be expecting "mm/dd/yyyy", meaning a date of "13/12/2016" (today)" would be invalid. Try using a format of yyyy-mm-dd, or dd-mmm-yyyy.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Dave

Have you considered using the DATEFROMPARTS function?

John

hb21l6 SSCommitted Points: 1647 More actions · Answer 3

Hi, Data looks like.

[invoice_number] [invoice_date][eta_date]

0009036414/09/0607/09/06

0009036514/09/0608/09/06

0009036614/09/0613/09/06

0009036714/09/0607/09/06

hb21l6 SSCommitted Points: 1647 More actions · Answer 4

and once I convert it, it looks like

000903642006-09-14 00:00:00.0002006-09-07 00:00:00.000

000903652006-09-14 00:00:00.0002006-09-08 00:00:00.000

000903662006-09-14 00:00:00.0002006-09-13 00:00:00.000

000903672006-09-14 00:00:00.0002006-09-07 00:00:00.000

so, not sure, why it wont accept it.

if I create an insert statement e.g. "insert into tbl_name (field) values ('2006-09-07 00:00:00.000') "

then it accepts it just fine.

hb21l6 SSCommitted Points: 1647 More actions · Answer 5

Thanks John,

I've tried using DateFromParts() and I get the same error.

,DATEFROMPARTS('20'+right(invoice_date,2),right(left(invoice_date,5),2),left(invoice_date,2)) as invoice_date

It's almost like something else is throwing it out, and not these fields. I'll keep digging.

Thom A SSC Guru Points: 99133 More actions · Answer 6

Do you need it as a Datetime? If you don't need/aren't storing the time, then use the datatype DATE.

Does it therefore manage to store any rows? As I said before, sample data and DDL is really what we need, we can replicate what you have then.

Me doing the following works, which means that the sample data you've provided doesn't have the problem, but another row does. You'll need to find an offending row in your sample data:

CREATE TABLE #Invoice (Invoice_number VARCHAR(10),

Invoice_date VARCHAR(8),

call_eta_date VARCHAR(8));

INSERT INTO #Invoice (Invoice_number, Invoice_date, call_eta_date)

VALUES ('00090364','14/09/06','07/09/06'),

('00090365','14/09/06','08/09/06'),

('00090366','14/09/06','13/09/06'),

('00090367','14/09/06','07/09/06');

SELECT [invoice_number],

Invoice_date,

call_eta_date,

convert(datetime,'20'+right(invoice_date,2)+'-'+ right(left(invoice_date,5),2)+'-'+left(invoice_date,2) ,102) as invoice_date, --try to keep your commas either left or right alligned ;)

convert(datetime,'20'+right(call_eta_date,2)+'-'+ right(left(call_eta_date,5),2)+'-'+left(call_eta_date,2) ,102) as call_eta_date

FROM #Invoice;

CREATE TABLE #Invoice_new (Invoice_number VARCHAR(10),

Invoice_date DATETIME,

call_eta_date DATETIME);

INSERT INTO #Invoice_new

SELECT [invoice_number],

convert(datetime,'20'+right(invoice_date,2)+'-'+ right(left(invoice_date,5),2)+'-'+left(invoice_date,2) ,102) as invoice_date, --try to keep your commas either left or right alligned ;)

convert(datetime,'20'+right(call_eta_date,2)+'-'+ right(left(call_eta_date,5),2)+'-'+left(call_eta_date,2) ,102) as call_eta_date

FROM #Invoice;

SELECT *

FROM #Invoice_new;

DROP TABLE #Invoice;

DROP TABLE #Invoice_new;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

-- Note1: CONVERT style 102 = yyyy.mm.dd

-- Note2: Use ISDATE with a character expression to determine if the expression can be converted to date.

-- isdate(convert(datetime won't work because the conversion will fail before ISDATE operates

-- check your dates by eye

SELECT TOP(1000)

[invoice_number],

x.*

FROM tbl_OldHeadTable oht

CROSS APPLY (

SELECT

[Invoice_Year] = '20' + right(invoice_date,2),

[Invoice_Month] = right(left(invoice_date,5),2),

[Invoice_DOM] = left(invoice_date,2),

[call_eta_Year] = '20' + right(call_eta_date,2),

[call_eta_Month] = right(left(call_eta_date,5),2),

[call_eta_DOM] = left(call_eta_date,2)

) x

WHERE NOT EXISTS (SELECT 1 FROM tbl_InvoiceHead ih WHERE ih.[invoice_number] = oht.[invoice_number])

AND ([Invoice_Year] > '2016' OR [Invoice_Month] > '12' OR [Invoice_DOM] > 31) -- adjust accordingly

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 8

You can use the convert function with the third parameter - style. According to your example you should use style number 3:

create table #Demo (c char(8))

insert into #Demo (c) values ('14/09/06'),('07/09/06')

select convert(datetime, c, 3)

from #Demo

drop table #Demo

By the way - why do you use varchar type to store dates?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

suggest you use TRY_CONVERT...iirc it wont throw error but pass NULL instead on data that cannot be converted

CREATE TABLE #Invoice (Invoice_number NVARCHAR(10),

Invoice_date NVARCHAR(8),

call_eta_date NVARCHAR(8));

INSERT INTO #Invoice (Invoice_number, Invoice_date, call_eta_date)

VALUES ('00090364','14/09/06','07/09/06'),

('00090365','14/09/06','08/09/06'),

('00090366','14/09/06','13/09/06'),

('00090367','14/09/06','07/09/06'),

('0009999','31/09/06','07/14/06'); --- note poor formed dates

CREATE TABLE #Invoice_new (Invoice_number NVARCHAR(10),

Invoice_date DATETIME,

call_eta_date DATETIME);

SET DATEFORMAT DMY;

INSERT INTO #Invoice_new

SELECT invoice_number,

TRY_CONVERT(DATETIME,Invoice_date) as invoice_date,

TRY_CONVERT(DATETIME,call_eta_date) as call_eta_date

FROM #Invoice;

SELECT *

FROM #Invoice_new;

DROP TABLE #Invoice;

DROP TABLE #Invoice_new;

edit added poor formed date as example

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

hb21l6 SSCommitted Points: 1647 More actions · Answer 10

Thanks for all the responses. its now resolved. (user error). enough said. :rolleyes:

I didn't know about try_convert which is a very useful function so at least I've learnt something new.

Thanks again.