August 4, 2008 at 2:16 am
I'm having an interesting challenge. After having spent several hours looking for a solution using Google, I thought it'd be better to go straight to the SQL-gurus right away. 🙂
The thing is that I have this table field that is populated with a string value, which should be a valid date. For example, 13th July, 2008 would be entered like 13-07-08. I cannot change this (it's been hard coded in another programme), so I'll have to make the best of it.
To be able to work with it I'm converting it to datetime format using CAST. This works, as long as there is valid 'date' in that field. Oh, I should mention that if there's no date set, the value could be either 00-00-00 or 99-99-99. In that case, I'm replacing the original date with NULL.
The problem is that people can enter any value into this field manually. So for example, they could enter 00-00-0 (forgetting the last 0). Or they could type something else that cannot be converted, like 00-00-08. Or who knows what somebody will come up with tomorrow. :w00t:
Anyway, what I'd really like is using a query that catches such conversion problems. Instead of quitting processing the query completely, it's fine if problematic 'dates' are replaced with NULL. Is that possible?
For your reference, here's the query I'm using right now:
(SELECT CASE orgvalue
WHEN '99-99-99' THEN NULL
WHEN '00-00-00' THEN NULL
ELSE
CAST(('20' + RIGHT(orgvalue,2) + '-' +
(select case SUBSTRING(orgvalue,CHARINDEX('-',orgvalue)+2,1)
when '-' then '0' + SUBSTRING(orgvalue,CHARINDEX('-',orgvalue)+1,1)
ELSE SUBSTRING(orgvalue,CHARINDEX('-',orgvalue)+1,2)
END) + '-' +
(select case SUBSTRING(orgvalue,2,1)
when '-' then '0' + LEFT(orgvalue,1) + 'T00:00:00'
else LEFT(orgvalue,2) + 'T00:00:00'
end)) as datetime)
END) AS realDateTime
FROM sourcetable
August 4, 2008 at 3:06 am
First of all I would say that the input should be checked in the application side, but, if you can't do it otherwise, you can check for conversion errors with some try/catch statement:
BEGIN TRY
SET @parsedDate = CAST(@inputDate AS DateTime)
END TRY
BEGIN CATCH
SET @parsedDate = NULL
END CATCH
... or something similar.
Hope this helps
Gianluca
-- Gianluca Sartori
August 4, 2008 at 3:13 am
You could also use the ISDATE system function (and set before it the LANGUAGE or DATEFORMAT to specify the format you want to check)
Regards,
Andras
August 7, 2008 at 5:53 am
Thank you both for your help! I've solved it by using the ISDATE funktion, and by temporarily setting the language to... Italian. 😎 Apparently, they use the dd-mm-jj notation a lot also. It works great!
Thanks again,
Ronald
August 7, 2008 at 6:03 am
Thanks Italy! 😀
-- Gianluca Sartori
August 8, 2008 at 3:11 am
rbeuker (8/7/2008)
and by temporarily setting the language to... Italian. 😎 Apparently, they use the dd-mm-jj notation a lot also.
you could also use "set datetime dmy" (in case the Italians change their date notation 🙂 )
Matte
August 8, 2008 at 4:15 am
Matte (8/8/2008)
rbeuker (8/7/2008)
and by temporarily setting the language to... Italian. 😎 Apparently, they use the dd-mm-jj notation a lot also.you could also use "set datetime dmy" (in case the Italians change their date notation 🙂 )
Matte
Nice one, grazie mille! (Thanks a lot) 😀
I'm now temporarily setting the dateformat to dmy, and back to mdy again:
set dateformat dmy
--my query goes here
set dateformat mdy
It goes without saying that having made this change does not mean that I would not like Italian(s) anymore! 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply