ISDATE() - Checks if the given value, is a valid date, time, or datetime. Returns 1 for success, 0 for failure.
Examples:
Select ISDATE('PRAGIM') -- returns 0
Select ISDATE(Getdate()) -- returns 1
Select ISDATE('2012-08-31 21:02:04.167') -- returns 1
Note: For datetime2 values, IsDate returns ZERO.
Example:
Select ISDATE('2012-09-01 11:34:21.1918447') -- returns 0.
Day() - Returns the 'Day number of the Month' of the given date
Examples:
Select DAY(GETDATE()) -- Returns the day number of the month, based on current system datetime.
Select DAY('01/31/2012') -- Returns 31
Month() - Returns the 'Month number of the year' of the given date
Examples:
Select Month(GETDATE()) -- Returns the Month number of the year, based on the current system date and time
Select Month('01/31/2012') -- Returns 1
Year() - Returns the 'Year number' of the given date
Examples:
Select Year(GETDATE()) -- Returns the year number, based on the current system date
Select Year('01/31/2012') -- Returns 2012
DateName(DatePart, Date) - Returns a string, that represents a part of the given date. This functions takes 2 parameters. The first parameter 'DatePart' specifies, the part of the date, we want. The second parameter, is the actual date, from which we want the part of the Date.
Valid Datepart parameter values
Examples:
Select DATENAME(Day, '2012-09-30 12:43:46.837') -- Returns 30
Select DATENAME(WEEKDAY, '2012-09-30 12:43:46.837') -- Returns Sunday
Select DATENAME(MONTH, '2012-09-30 12:43:46.837') -- Returns September
A simple practical example using some of these DateTime functions. Consider the table tblEmployees.
Write a query, which returns Name, DateOfBirth, Day, MonthNumber, MonthName, and Year as shown below.
Query:
Select Name, DateOfBirth, DateName(WEEKDAY,DateOfBirth) as [Day],
Month(DateOfBirth) as MonthNumber,
DateName(MONTH, DateOfBirth) as [MonthName],
Year(DateOfBirth) as [Year]
From tblEmployees
Examples:
Select ISDATE('PRAGIM') -- returns 0
Select ISDATE(Getdate()) -- returns 1
Select ISDATE('2012-08-31 21:02:04.167') -- returns 1
Note: For datetime2 values, IsDate returns ZERO.
Example:
Select ISDATE('2012-09-01 11:34:21.1918447') -- returns 0.
Day() - Returns the 'Day number of the Month' of the given date
Examples:
Select DAY(GETDATE()) -- Returns the day number of the month, based on current system datetime.
Select DAY('01/31/2012') -- Returns 31
Month() - Returns the 'Month number of the year' of the given date
Examples:
Select Month(GETDATE()) -- Returns the Month number of the year, based on the current system date and time
Select Month('01/31/2012') -- Returns 1
Year() - Returns the 'Year number' of the given date
Examples:
Select Year(GETDATE()) -- Returns the year number, based on the current system date
Select Year('01/31/2012') -- Returns 2012
DateName(DatePart, Date) - Returns a string, that represents a part of the given date. This functions takes 2 parameters. The first parameter 'DatePart' specifies, the part of the date, we want. The second parameter, is the actual date, from which we want the part of the Date.
Valid Datepart parameter values
Examples:
Select DATENAME(Day, '2012-09-30 12:43:46.837') -- Returns 30
Select DATENAME(WEEKDAY, '2012-09-30 12:43:46.837') -- Returns Sunday
Select DATENAME(MONTH, '2012-09-30 12:43:46.837') -- Returns September
A simple practical example using some of these DateTime functions. Consider the table tblEmployees.
Write a query, which returns Name, DateOfBirth, Day, MonthNumber, MonthName, and Year as shown below.
Query:
Select Name, DateOfBirth, DateName(WEEKDAY,DateOfBirth) as [Day],
Month(DateOfBirth) as MonthNumber,
DateName(MONTH, DateOfBirth) as [MonthName],
Year(DateOfBirth) as [Year]
From tblEmployees
Hi,
ReplyDeleteThis is very excellent tutorial and teaching. Keep this up. I have watched so many tutorial but no one can teach like you. Excellent. I woul like to find out where would i get how to insert dateofbirth, age into the already exciting table?
Really nice tutorials and i recommended it to all my students
ReplyDeleteI love watching your videos, you make everything so easy to understand! Thank you so much for all your time and effort!
ReplyDeleteHi Venkat,
ReplyDeleteNote: For datetime2 / date values, IsDate returns an Error not Zero.
Msg 8116, Level 16, State 1, Line 50
Argument data type date is invalid for argument 1 of isdate function.
Regards
Gagandeep Singh