Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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.

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.

Select ISDATE('2012-09-01 11:34:21.1918447') -- returns 0.

Day() - Returns the 'Day number of the Month' of the given date

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

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

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

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.

Select Name, DateOfBirth, DateName(WEEKDAY,DateOfBirth) as [Day], 
            Month(DateOfBirth) as MonthNumber, 
            DateName(MONTH, DateOfBirth) as [MonthName],
            Year(DateOfBirth) as [Year]
From   tblEmployees

1 comment:

  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?


If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.