How to convert a string into datetime, if fails return null

Question

How to convert a string into datetime, if fails return null

mr.zl8888

SSC Eights!

Points: 815
More actions
June 5, 2012 at 12:00 am

#392429

Hi Guys,
I am having a project need to convert a varchar(255) column from a table into datetime, if the the value in the column failed on convert, give a null, other wise return the data in datetime format.
On SQL2012 there is a new build-in function called TRY_CONVERT, which can check a convert and return a null if it fails.
(http://qa.sqlservercentral.com/blogs/basits-sql-server-tips/2012/05/26/new-built-in-functions-in-sql-server-2012/)
How can we achieve the same thing in SQL2008R2?
Thanks a lot.
June 5, 2012 at 5:59 am

This was removed by the editor as SPAM

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

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

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

You could use the parse function in this article[/url] and tweak it to return NULL in case a bad date is found.

However, that would require changing the function from ITVF to MSTVF to introduce the TRY/CATCH. I'm pretty sure this will slow down things a lot.

I would look into CLR for this kind of things. CLR is faster and easier to write and manage.

Hope this helps

Gianluca

-- Gianluca Sartori

Lowell SSC Guru Points: 323482 More actions · Answer 2

Nice Article Gianluca!

I added your CLR to my collection; very much appreciated.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spaghettidba SSC Guru Points: 105712 More actions · Answer 3

Thanks Lowell!

Glad you liked it.

-- Gianluca Sartori

mr.zl8888 SSC Eights! Points: 815 More actions · Answer 4

Gianluca Sartori (6/5/2012)
You could use the parse function in this article[/url] and tweak it to return NULL in case a bad date is found.
However, that would require changing the function from ITVF to MSTVF to introduce the TRY/CATCH. I'm pretty sure this will slow down things a lot.
I would look into CLR for this kind of things. CLR is faster and easier to write and manage.
Hope this helps
Gianluca

Thanks Gianluca, very good article.

What's "MSTVF" stands for? We can't put a try/catch block inside function, can we?

Before I write a CLR function for convert a string to datetime with failed return null, could we do something in t-sql, so I don't need to convince my manager to allow me to write a CLR?:-)

I am using a function(datevalue varchar(255),DateFormat(64)) to convert a column into datetime with dynamic DateFormat. So I can't use ISDATE(), because I need to use SET DATEFORMAT to preset a date format.

Thanks

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

What is the actual format of the strings that you are trying to convert to datetime?

mr.zl8888 SSC Eights! Points: 815 More actions · Answer 6

Michael Valentine Jones (6/5/2012)
What is the actual format of the strings that you are trying to convert to datetime?

Hi Michael,

The project is loading a csv file into a staging table with 255 columns(from col1 to col255) with varchar(255) on each column.

Another table called data_column_definition has a template of each column, such as col1 varchar(255), col2 datetime, format 103, col int,... etc.

The format of datetime can be changed due to different column template.

All I have to do is write a dynamic sql to match each column in column definition table with staging table. I can get this query done, no problem with that. The only requirement is if a convert failed, need to return a null, other than give a convert failed error message.

Such as:

SELECT dbo.FormatString(Col2,NULL) AS Name,

dbo.FormatDatetime(Col3,103) AS Date, -- col3 is varchar(255), FormatDatetime is a function does convert

dbo.FormatINT(Col5,NULL) AS Qty

FROM tableA

Result need to like this:

peter 2012-05-16 00:00:00.000 1

emily NULL 2

sean 2012-05-16 00:00:00.000 1

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

Stewart's method is about the only way in pure T-SQL to perform that task, using IsDate() and then converting or returning null based on the result of IsDate().

Just make sure you return NULL first on a failure and not the other way around, so the CASE statement can short-circuit the attempted conversion.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mr.zl8888 SSC Eights! Points: 815 More actions · Answer 8

Thanks for all your reply.

Probably, I need add a date format field on my template table, and using SET DATEFORMAT at the beginning of the dynamic query and try ISDate() function.

I am so glad SQL2012 introduced new build-in try_convert() function and format() function. :-D:-D:-D

spaghettidba SSC Guru Points: 105712 More actions · Answer 9

mr.zl8888 (6/5/2012)
Gianluca Sartori (6/5/2012)
You could use the parse function in this article[/url] and tweak it to return NULL in case a bad date is found.
However, that would require changing the function from ITVF to MSTVF to introduce the TRY/CATCH. I'm pretty sure this will slow down things a lot.
I would look into CLR for this kind of things. CLR is faster and easier to write and manage.
Hope this helps
Gianluca
Thanks Gianluca, very good article.
What's "MSTVF" stands for? We can't put a try/catch block inside function, can we?

MSTVF = Multi Statement Table Valued Function.

It's the only way to have a try/catch block inside a function.

-- Gianluca Sartori