ISDATE() function in SQL

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 DATEFORMAT.

WE CAN USE THIS FUNC TO CHECK WHETHER GIVEN DATE IS VALID OR NOT BY HLPING WITH SETTING SET 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. 

---------------------------------------------------------------



--------------------------------------------------------

What are tables has specified column in a database?



SELECT tab.name AS Table_Name,
SCHEMA_NAME(schema_id) AS SCHEMA_NAME,
coltab.name AS COLUMN_NAME,tab.CREATE_DATE,tab.MODIFY_DATE
FROM sys.tables AS tab
INNER JOIN sys.columns coltab ON tab.OBJECT_ID = coltab.OBJECT_ID
WHERE coltab.name LIKE '%name%' -- give column name which one you want to know
ORDER BY schema_name, table_name;


Additionally want to know this:
Diffault access modifiers in C#


What is the max number of parameters that can be passed in stored procedure?

Subscribe to: Posts (Atom)