Convert to Datetime in Datediff

Question

Convert to Datetime in Datediff

sharonsql2013

SSCertifiable

Points: 5908
More actions
January 6, 2015 at 11:20 am

#313484

I am having a hard time converting Record_Created which is Varchar to datetime in Datediff function
Here is the datediff exp
Datediff(dd,Record_Created,getdate()) as CreationDate

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

What's the problem? Could you post sample data? Could you change the column to be a date/time data type?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 2

Its bad data... No I don't have access to changing datatypes

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

Can you show us a sample of the data in the Record_Created column?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

sharonsql2013 (1/6/2015)
Its bad data... No I don't have access to changing datatypes

And the answers for the first 2 questions?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 5

Sample :

21 Dec 2014 00:16:15

25 Oct 2014 00:16:18

30 Dec 2014 00:16:53

90000

23 Oct 2013 00:16:27

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

sharonsql2013 (1/6/2015)
Sample :
21 Dec 2014 00:16:15
25 Oct 2014 00:16:18
30 Dec 2014 00:16:53
90000
23 Oct 2013 00:16:27

Which is why you should never store date/time values in a character field. What does 90000 represent? Does the application properly render a date for that value?

Ed Wagner SSC Guru Points: 287024 More actions · Answer 7

sharonsql2013 (1/6/2015)
Sample :
21 Dec 2014 00:16:15
25 Oct 2014 00:16:18
30 Dec 2014 00:16:53
90000
23 Oct 2013 00:16:27

You probably already realize that it's always best to select an appropriate data type during design so you don't end up with this situation in the first place, so I'm not going to harp on it.

Is this sample representative of all types of data you have, both good and bad? Or do you have other formats of bad data in the table?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

You need to create validations for your data.

CREATE TABLE cDatesTest(

Record_Created varchar(30));

INSERT cDatesTest VALUES

('21 Dec 2014 00:16:15'),

('25 Oct 2014 00:16:18'),

('30 Dec 2014 00:16:53'),

('90000'),

('23 Oct 2013 00:16:27')

SELECT Datediff(dd,Clean.Record_Created,getdate()) as CreationDate

FROM cDatesTest

CROSS APPLY( SELECT CASE WHEN Record_Created LIKE '%[0-9] [A-Z][a-z][a-z] [1-2][09][0-9][0-9]%'

THEN Record_Created

ELSE NULL END)Clean(Record_Created)

GO

DROP TABLE cDatesTest

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ed Wagner SSC Guru Points: 287024 More actions · Answer 9

Nice, Luis. I didn't consider using regular expression. I went down the road of splitting the parts of the date and then concatenating them and converting the result into a datetime.

CREATE TABLE dbo.bad_dates (

record_created varchar(32));

INSERT INTO dbo.bad_dates(record_created)

VALUES('21 Dec 2014 00:16:15'),

('25 Oct 2014 00:16:18'),

('30 Dec 2014 00:16:53'),

('90000'),

('23 Oct 2013 00:16:27');

WITH cteSplit AS (

SELECT d.record_created,

MAX(CASE WHEN s.itemnumber = 2 THEN s.item END) month_part,

MAX(CASE WHEN s.itemnumber = 1 THEN s.item END) day_part,

MAX(CASE WHEN s.itemnumber = 3 THEN s.item END) year_part,

MAX(CASE WHEN s.itemnumber = 4 THEN s.item END) time_part

FROM dbo.bad_dates d

CROSS APPLY DelimitedSplit8K(d.record_created, ' ') s

GROUP BY d.record_created

)

SELECT CONVERT(datetime, month_part + ' ' + day_part + ', ' + year_part + ' ' + time_part)

FROM cteSplit

WHERE month_part IS NOT NULL;

Please note that Luis's approach does the filtering by using regular expressions, so the bad data is eliminated up front.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions