Tags


Many times I have heard programmers saying that they are not comfortable dealing with Date calculations. Being a programmer , I can say that it is really very essential and powerful feature and from time to time we have to use various kind of date functions for various purposes.

So I thought it would be better to combine all the date time functions that I normally use in this blog, so that in future it would be easier for you to use them while developing codes.

GETDATE- Returns the current system date and time.

SELECT GETDATE()

/*

Output:

————–

2010-03-15 00:56:14.043

*/

DAY- Returns an integer representing the day (day of the month) of the specified date.

SELECT DAY(GETDATE()) as ‘TodaysDay’

/*

Output:

————–

15

*/

MONTH- Returns an integer that represents the month of the specified date.

SELECT MONTH(’03/15/2010′) as ‘CurrentMonth’

/*

Output:

————-

3

*/

YEAR- Returns an integer that represents the year of the specified date

SELECT YEAR(’03/15/2010′) as ‘CurrentYear’

/*

Output:

————-

2010

*/

DATENAME- Returns a character string that represents the specified datepart of the specified date.

Syntax: DATENAME (datepart, date)

SELECT DATENAME (YY,’03/15/2010′) as ‘CurrentYear’

SELECT DATENAME(QQ, ’03/15/2010′) as ‘CurrentQuarter’

SELECT DATENAME(DW,’03/15/2010′) as ‘CurrentWeekDay’

/*

Output:

————-

2010

1

Monday

*/

DATEPART- Returns an integer that represents the specified datepart of the specified date.

Syntax: DATEPART (datepart,date)

SELECT DATEPART (wk,’03/15/2010′) as ‘CurrentWeek’

/*

Output:

————-

12

*/

DATEADD – Returns a new datetime value based on adding an interval to a specified date.

Syntax: DATEADD ( datepart, number, date)

— Adding one year with the given date and fetching the output

SELECT DATEADD (yy, 1,’03/15/2010′) as ‘NextYear’

/*

Output:

————-

2010-03-15 00:00:00.000

*/

DATEDIFF – Returns the difference between two dates

Syntax: DATEDIFF ( datepart, startdate, enddate)

SELECT DATEDIFF (yy,’03/15/2005′,’03/15/2010′) as ‘YearDifference’

/*

Output:

————-

5

*/

I think I have covered the basic date functions that we normally use in our coding. In the next edition I will show how we can use these basic functions to calculate complex date queries. Till then enjoy learning..

Cheers,

Subhro Saha

Advertisements