Hi Foxs, its simple but some of us forget to use this at the time of RUSH hours.
ISDATE() is sql function which is used to check the given string is eligible to date formate with the help of
ISDATE() is sql function which is used to check the given string is eligible to date formate with the help of
DATEFORMAT.WE CAN USE THIS FUNC TO CHECK WHETHER GIVEN DATE IS VALID OR NOT BY HLPING WITH SETTING DATEFORMAT and SET LANGUAGE
SET DATEFORMAT mdy;
SELECT ISDATE('15/04/2008'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('15/2008/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/15/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/04/15'); --Returns 1.
CAUSE :
THE ISDATE() return values depends on the settings set by SET DATEFORMAT , SET LANGUAGE and default language option.
/* Use these
sessions settings. */
SET LANGUAGE us_english;
SET DATEFORMAT mdy;
/* Expression in
mdy dateformat */
SELECT ISDATE('04/15/2008'); --Returns 1.
/* Expression in
mdy dateformat */
SELECT ISDATE('04-15-2008'); --Returns 1.
/* Expression in
mdy dateformat */
SELECT ISDATE('04.15.2008'); --Returns 1.
/* Expression in
myd dateformat */
SELECT ISDATE('04/2008/15'); --Returns 1.
SET DATEFORMAT mdy;
SELECT ISDATE('15/04/2008'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('15/2008/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/15/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/04/15'); --Returns 1.
SET DATEFORMAT dmy;
SELECT ISDATE('15/04/2008'); --Returns 1.
SET DATEFORMAT dym;
SELECT ISDATE('15/2008/04'); --Returns 1.
SET DATEFORMAT ydm;
SELECT ISDATE('2008/15/04'); --Returns 1.
SET DATEFORMAT ymd;
SELECT ISDATE('2008/04/15'); --Returns 1.
DIFFERENT SQL LANGUAGE
SET LANGUAGE Italian;
SELECT ISDATE('2008/04/15'); --Returns 1.
SET LANGUAGE Hungarian;
SELECT ISDATE('15/2008/04'); --Returns 0.
SET LANGUAGE English;
SELECT ISDATE('15/04/2008'); --Returns 0.
SET LANGUAGE Swedish;
SELECT ISDATE('2008/15/04'); --Returns 0.
/* Return to
these sessions settings. */
SET LANGUAGE us_english;
SET DATEFORMAT mdy;
Messsages:
Changed language setting to us_english.
---------------------------------------------------------------
--------------------------------------------------------
---------------------------------------------------------------
--------------------------------------------------------
