Tuesday, June 13, 2006

Check For Valid SMALLDATETIME

Sometime you get data from different sources and the dates are stored in a varchar columnYou need to store that data in your database. The column has a smalldatetime data typeHow do you make sure that the data is correct? You have to use ISDATE and also check that the dates are between 1/1/1900 and 6/6/2079
Here is some code to test that out

CREATE TABLE TestDates(VarCharDate VARCHAR(30))


-- Good smalldatetime
INSERT TestDates
VALUES ('19000101')

--Bad smalldatetime, less than 19000101
INSERT TestDates
VALUES ('18990101')

--Bad smalldatetime, greater than 20790606
INSERT TestDates
VALUES ('20790607')

-- Good smalldatetime
INSERT TestDates
VALUES ('20790606')


--Bad date
INSERT TestDates
VALUES ('abababa')

--This will Fail
SELECT CASE
WHEN ISDATE(VarCharDate) =1 THEN
CONVERT(SMALLDATETIME,VarCharDate)
ELSE
NULL
END
FROM
TestDates

--This will run fine

SELECT VarCharDate,
CASE
WHEN ISDATE(VarCharDate) =1
THEN CASE WHEN VarCharDate BETWEEN '19000101' AND '20790606'
THEN CONVERT(SMALLDATETIME,VarCharDate)
ELSE NULL
END
END
FROM
TestDates


Flo has left a comment, he/she has suggested to convert to datetime in the second case in the case that the date is not stored in the YYYYMMDD format
Here is the suggestion

DECLARE @VarCharDate VARCHAR(100)
SET @VarCharDate = '12/30/2070'

-- Modification:
-- Use CONVERT(DATETIME, @VarCharDate) to check the varchar in any possible format

SELECT @VarCharDate,
CASE
WHEN ISDATE(@VarCharDate) =1
THEN CASE WHEN CONVERT(DATETIME,@VarCharDate) BETWEEN '19000101' AND '20790606'
THEN CONVERT(SMALLDATETIME,@VarCharDate)
ELSE NULL
END
END











3 comments:

Anonymous said...

Hello

Good function! To ensure that it works also with other possible datetime-strings, not only format 'yyyymmdd', I would suggest to make a COMVERT to DATETIME within the BETWEEN-Check:


DECLARE @VarCharDate VARCHAR(100)
--SET @VarCharDate = '20700101'
SET @VarCharDate = '12/30/2070'

-- Your code
SELECT @VarCharDate,
CASE
WHEN ISDATE(@VarCharDate) =1
THEN CASE WHEN @VarCharDate BETWEEN '19000101' AND '20790606'
THEN CONVERT(SMALLDATETIME,@VarCharDate)
ELSE NULL
END
END

-- Modification:
-- Use CONVERT(DATETIME, @VarCharDate) to check the varchar in any possible format
SELECT @VarCharDate,
CASE
WHEN ISDATE(@VarCharDate) =1
THEN CASE WHEN CONVERT(DATETIME, @VarCharDate) BETWEEN '19000101' AND '20790606'
THEN CONVERT(SMALLDATETIME,@VarCharDate)
ELSE NULL
END
END


Greets
Flo

SQL said...

True, I will add your piece of code to the post

Thanks

Anonymous said...

That was cool.
I have a problem statement..

Consider the following time ranges during which an activity X might take place
09:30 -> 10:30
10:30 -> 11:30
11:00 -> 12:00

The total time taken for the activity X will be 02hrs 30 mins ( there is a 30 mins overlap between the 2nd and 3rd time ranges).
Any idea on how to solve this ??