IsDate, Day, Month, Year and DateName DateTime functions in SQL Server - Part 26

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

4 comments:

  1. Hi,
    This 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?

    ReplyDelete
  2. Really nice tutorials and i recommended it to all my students

    ReplyDelete
  3. I love watching your videos, you make everything so easy to understand! Thank you so much for all your time and effort!

    ReplyDelete
  4. Hi Venkat,

    Note: 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

    ReplyDelete

It would be great if you can help share these free resources