Casting/converting date time stored as text

Question

Post reply

Casting/converting date time stored as text

david 70530

SSC Veteran

Points: 256
More actions
October 19, 2015 at 2:18 pm

#321560

Noob here looking for some basic help. Despite seeing many articles on the web about this topic I can't seem to make it work.
I have a DB in SQL Express 2014 that contains data that I have imported from a few dozen txt files. During the import that date-time data came in as a nvarchar(255) field and I would like to cast/convert (not 100% sure of the difference) that into another field storing it as datetime2. The data in the nvarchar field is formatted as: 2015-03-01 01:00:00.00
I have renamed the nvarchar field date_time_txt and created a new datetime2 field called date_time. I have tried the statements below, but receive Msg 241, Conversion from character string failed errors each time.
Try 1:
update hourly
set date_time= cast(date_time_txt as datetime2)
Try 2:
update hourly
set date_time = convert(nvarchar,date_time_text,120)
Can anyone give some help on how to accomplish this?
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Run the following:

select date_time_txt, try_parse(date_time_txt as datetime2) testConvert

from hourly

where try_parse(date_time_txt as datetime2) is null;

Any results returned?

david 70530 SSC Veteran Points: 256 More actions · Answer 2

Thanks Lynn. I don't fully follow what the code does, but here are the results. Can you fill me in? FYI there are 36,283,917 records in the db.

Thanks

date_time_txttestConvert

NULLNULL

2015-08-01 00:NULL

NULLNULL

2015NULL

NULLNULL

2015-08-0NULL

NULLNULL

1NULL

0NULL

NULLNULL

13087NULL

NULLNULL

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

david 70530 (10/19/2015)
Thanks Lynn. I don't fully follow what the code does, but here are the results. Can you fill me in? FYI there are 36,283,917 records in the db.
Thanks
date_time_txttestConvert
NULLNULL
NULLNULL
2015-08-01 00:NULL
NULLNULL
NULLNULL
NULLNULL
NULLNULL
NULLNULL
2015NULL
NULLNULL
2015-08-0NULL
NULLNULL
NULLNULL
1NULL
0NULL
NULLNULL
13087NULL
NULLNULL
NULLNULL
NULLNULL
NULLNULL
NULLNULL
NULLNULL
NULLNULL
NULLNULL

What it is showing you are the text values that won't convert to a datetime2 value.

david 70530 SSC Veteran Points: 256 More actions · Answer 4

Interesting. I'm assuming those are as a result of some error in the format etc that it's not able to interpret? Relative to how many records I have that tiny of amount of errors is nothing.

How can I make it convert the ones it can't and just leave the others blank or skip them?

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 5

david 70530 (10/19/2015)
How can I make it convert the ones it can't and just leave the others blank or skip them?

update hourly

set date_time= try_parse(date_time_txt as datetime2)

-- WHERE NullIf(date_time_txt, '') IS NOT NULL -- optional - only update where a date exists

Find "errors" which can then be fixed:

SELECT *

FROM hourly

WHERE date_time IS NULL

AND NullIf(date_time_txt, '') IS NOT NULL

or find only error first so they can be fixed before the update:

SELECT *

FROM hourly

WHERE try_parse(date_time_txt as datetime2) IS NULL

AND NullIf(date_time_txt, '') IS NOT NULL

david 70530 SSC Veteran Points: 256 More actions · Answer 6

Thanks Kristen

First I used the statement to figure out the trouble makers and there were about 8 or so records. I was going to delete them but decided it wasn't worth it. So I used your update statement and I'm in business now.

Just for my understanding, does using the try_parse statement essentially mean "if you can - do it; if not - ignore it and move on"?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

david 70530 (10/20/2015)
Thanks Kristen
First I used the statement to figure out the trouble makers and there were about 8 or so records. I was going to delete them but decided it wasn't worth it. So I used your update statement and I'm in business now.
Just for my understanding, does using the try_parse statement essentially mean "if you can - do it; if not - ignore it and move on"?

The functions try_parse and try_convert give you the opportunity to see if the desired conversion is going to work or not. You have to write your code to properly deal with it the appropriate manner depending on what you are doing.