Date/ Time Functions in SQL- Part-2

Hello friends, in my last article we talked about few Date/Time functions available in SQL Server 2008, in the continuation of my earlier blog, we will talk about few more Date/Time functions in this article.

DAY(Date) :- DAY function takes one parameter as an input and it returns Day part of the date supplied as input parameter. Lets execute below SQL Statement and see the result.

Select DAY('30 July 2005')

In the above screenshot, we can see that as an input, we supplied “30 July 2005” and as a result, we got 30 which is the “Day” part of the date supplied.

MONTH(Date):- MONTH function takes one parameter as an input and it returns Month part of the date supplied as input parameter. Lets execute below SQL Statement and see the result.

Select Month('30 July 2005')

In the above screenshot, we can see that as an input, we supplied “30 July 2005” and as a result, we got 7 which is the Month(July is the 7^th Month of the year) part of the date supplied.

YEAR(Date):- YEAR function takes one parameter as an input and it returns Year part of the date supplied as input parameter. Lets execute below SQL Statement and see the result.

Select YEAR('30 July 2005')

In the above screenshot, we can see that as an input, we supplied “30 July 2005” and as a result, we got 2005 which is the Year(2005) part of the date supplied.

DATEPART(date_Part,Date):- DATEPART function take two input parameters and returns an integer value indicating date_part(supplied as input date_part) of Date supplied.

DATENAME(date_part,Date):- DATENAME function take two input parameters and return a character string  indicating date_part(supplied as input date_part) of Date supplied.

Note:- DATEPART and DATENAME functions are quite similar to each other, the only difference we will see when we supply “MONTH” as data_part in both functions.

Let’s execute below query to see the difference.

Select DATEPART(Month,'30 July 2005') as Date_Part,DATENAME(Month,'30 July 2005') as DATE_Name

We can see in the above screenshot, in DATEPART function we are receiving (7) while in DATENAME , we are receiving (July). DATEPART is showing the result in numeric form while DATENAME is showing the result in Character form.

But if we execute below SQL Statement, we will get same results by both functions.

Select DATEPART(Day,'30 July 2005') as Date_Part,

DATENAME(Day,'30 July 2005') as DATE_Name

ISDATE(Expresion):- ISDATE function takes one input parameter as string expression and returns 0 or 1. If expression specified is a valid Date,Time, or DateTime then it returns 1 and if expression specified is not a valid Date,Time or DateTime it returns 0.

Lets execute below SQL Statements and see the results.

Select ISDATE('30-7-2005') Valid_Expression,

ISDATE('30,10,2010') Invalid_Expression

In the above screenshot, we can see that we have supplied one valid expression and one invalid expression and results are as expected.

Friends keep reading the blog and keep posting you feedback.