Date Conversion

Question

Post reply

Date Conversion

vaibhav.tiwari

SSCommitted

Points: 1976
More actions
April 1, 2010 at 5:21 am

#143726

Hi everyone,
I have one table and previously it was designed badly
and they put the Logdatetime column as varchar(50)
and date was stored in different formats or shit data may be stored.
so now when i m doing any date operation on that column
so i am getting converstion error.
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
How could i get those record where this error may occur if i will do any date operation.
Please help me out...
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Viewing 15 posts - 1 through 15 (of 22 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could use the following line to find the rows with bad data format:

SELECT * FROM YourTable WHERE ISDATE(yourColumn) = 0

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 2

lmu92 (4/1/2010)
You could use the following line to find the rows with bad data format:
SELECT * FROM YourTable WHERE ISDATE(yourColumn) = 0

Thanks a lot, My problem is solved, I never listent about this function.

thanks again.

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 3

One more thing some date is like '25-01-2010 09:51:59' that is the valid date but can not be converted into date.

what should I do.

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Paul White SSC Guru Points: 150467 More actions · Answer 4

vaibhav.tiwari (4/1/2010)
One more thing some date is like '25-01-2010 09:51:59' that is the valid date but can not be converted into date.

It's valid in my locale: 25 January 2010 09:51:59.

The value returned by ISDATE depends on session settings like DATEFORMAT and LANGUAGE.

All this is in Books Online:

ISDATE (Transact-SQL)

See example C.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

vaibhav.tiwari (4/1/2010)
One more thing some date is like '25-01-2010 09:51:59' that is the valid date but can not be converted into date.
what should I do.

If all your datetime strings are coded as above, the following code sample shows how you can covert the data to a datetime value:

declare @TestDateStr as varchar(24);

set @TestDateStr = '25-01-2010 09:51:59';

select @TestDateStr, convert(datetime, @TestDateStr, 105)

Paul White SSC Guru Points: 150467 More actions · Answer 6

Lynn Pettis (4/1/2010)
If all your datetime strings are coded as above, the following code sample shows how you can covert the data to a datetime value:

I read the question as asking how to identify rows with invalid values...?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Paul White NZ (4/1/2010)
Lynn Pettis (4/1/2010)
If all your datetime strings are coded as above, the following code sample shows how you can covert the data to a datetime value:
I read the question as asking how to identify rows with invalid values...?

Rereading the original post, it looks like a combination of garbage data and good data in different formats.

Paul White SSC Guru Points: 150467 More actions · Answer 8

Lynn Pettis (4/1/2010)
Rereading the original post, it looks like a combination of garbage data and good data in different formats.

Glad you used the word 'garbage' rather than the somewhat inappropriate term used in the original post.

Your code may well help him, so it's all good. 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 9

Paul White NZ (4/1/2010)
Lynn Pettis (4/1/2010)
Rereading the original post, it looks like a combination of garbage data and good data in different formats.
Glad you used the word 'garbage' rather than the somewhat inappropriate term used in the original post.
Your code may well help him, so it's all good. 🙂

I am sorry to use the wrong word in my post...

but yeah there is the combination of data

like dates which can be converted by default format

dates which are valid but can not convert in default format

garbage 🙂 data which are not at all date.

how can a single query give me the expected output

because by isdate function i can ommit the invalid date for default format

but in that case i may loose all the dates.

I hope you all understood my problem...

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Paul White SSC Guru Points: 150467 More actions · Answer 10

The following code is not fully robust (it may depend on evaluation order in places) but should give you some ideas.

DECLARE @Garbage

TABLE (

garbage_date VARCHAR(50) NOT NULL

);

INSERT @Garbage VALUES ('25-01-2005');

INSERT @Garbage VALUES ('01-25-2005');

INSERT @Garbage VALUES ('25 Jan 2005');

INSERT @Garbage VALUES ('2005/01/25');

INSERT @Garbage VALUES ('2005 Jan 25');

-- Returns NULL if ISDATE returns zero

SELECT ISDATE(G.garbage_date),

CASE

WHEN ISDATE(G.garbage_date) = 1

THEN CONVERT(DATETIME, G.garbage_date, 0)

ELSE NULL

END

FROM @Garbage G;

-- Returns only valid dates

SELECT CONVERT(DATETIME, G.garbage_date, 0)

FROM @Garbage G

WHERE ISDATE(G.garbage_date) = 1;

-- Returns invalid dates

SELECT G.garbage_date

FROM @Garbage G

WHERE ISDATE(G.garbage_date) = 0;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

You may not get it all in one pass is something you may have to consider. Converting the data to a datetime may require multiple passes through the data. Trying to do it in one pass may simply be too much at one time. How many rows of data do you have to process?

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 12

Hi Lynn

i have 38105166 data in one table. if i can get all the different format which are being used in that column then i can create a query for that. but i m not sure how many formats are being used.

I have 776543 records which are ommit with Isdate function or that are not valid dates which can be converted into datetime.

Thank you very much Paul for the reply.

But this is again question for what about null values

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Paul White SSC Guru Points: 150467 More actions · Answer 13

vaibhav.tiwari (4/2/2010)
Thank you very much Paul for the reply.
But this is again question for what about null values

ISDATE returns zero for a NULL date.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 14

Paul White NZ (4/2/2010)
vaibhav.tiwari (4/2/2010)
Thank you very much Paul for the reply.
But this is again question for what about null values
ISDATE returns zero for a NULL date.

Thanks for the reply

but it was not like that

I was asking that if i will use your query then it will give me null for all invalid date but that might be possible that that invalid date is like '25-01-2010' so it is proper date so i dont want to ommit that.

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together