Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Date Validations? Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 12:37 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721


--this date MAY or MAY NOT be valid depending on the DATEFORMAT setting
SELECT ISDATE('29-12-2013')
--this is never valid (out-of-range)
SELECT ISDATE('32-12-2013')
--this should always be valid in all formats
SELECT ISDATE('2013-01-08 15:44:12.208')
--this is a valid UMC date but ISDATE = 0
SELECT ISDATE('2013-01-08 15:44:12.2081606 +05:30')


DECLARE @testdate VARCHAR(20)
SET @testdate = '29-12-2013' -- this is valid in DMY format
SET @testdate = '32-12-2013' -- this is never valid (out-of-range)
SET @testdate = '2013-01-08 15:44:12.208' -- this is valid
SET @testdate = '2013-01-08 15:44:12.2081606 +05:30' -- this is valid UMC date

--for these testdates using the script below,
--#1 is ambiguous, #2 is correct, #3 & #4 are INCORRECT
SELECT
@testdate
,IsDateValid = CASE WHEN @testdate IS NULL THEN 0
WHEN @testdate NOT LIKE '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]' THEN 0
WHEN ISDATE(REPLACE(@testdate,'-','')) <> 1 THEN 0
ELSE 1
END

--this procedure returns the proper results
EXEC dbo.IsValidDate '29-12-2013','DMY'
EXEC dbo.IsValidDate '32-12-2013'
EXEC dbo.IsValidDate '2013-01-08 15:44:12.208'
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30'




Post #1476210
Posted Monday, July 22, 2013 12:54 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:39 AM
Points: 3,105, Visits: 11,494
Steven Willis (7/22/2013)


--this date MAY or MAY NOT be valid depending on the DATEFORMAT setting
SELECT ISDATE('29-12-2013')
--this is never valid (out-of-range)
SELECT ISDATE('32-12-2013')
--this should always be valid in all formats
SELECT ISDATE('2013-01-08 15:44:12.208')
--this is a valid UMC date but ISDATE = 0
SELECT ISDATE('2013-01-08 15:44:12.2081606 +05:30')


DECLARE @testdate VARCHAR(20)
SET @testdate = '29-12-2013' -- this is valid in DMY format
SET @testdate = '32-12-2013' -- this is never valid (out-of-range)
SET @testdate = '2013-01-08 15:44:12.208' -- this is valid
SET @testdate = '2013-01-08 15:44:12.2081606 +05:30' -- this is valid UMC date

--for these testdates using the script below,
--#1 is ambiguous, #2 is correct, #3 & #4 are INCORRECT
SELECT
@testdate
,IsDateValid = CASE WHEN @testdate IS NULL THEN 0
WHEN @testdate NOT LIKE '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]' THEN 0
WHEN ISDATE(REPLACE(@testdate,'-','')) <> 1 THEN 0
ELSE 1
END

--this procedure returns the proper results
EXEC dbo.IsValidDate '29-12-2013','DMY'
EXEC dbo.IsValidDate '32-12-2013'
EXEC dbo.IsValidDate '2013-01-08 15:44:12.208'
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30'




The OP stated that the total date should be in format YYYY-MM-DD, so the test I gave them looks for exactly that, and is insensitive to the setting of DATEFORMAT.

From OP:
"Year should be in yyyy format ,month should be between 1 and 1 to 12 and date should be between 1 to 31 and total date should be YYYY-MM-DD Formate?"


Post #1476218
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse