SQL Date Functions

here are some sql DATE related functions :

1. SET DATEFIRST 1 // set first day of the week as Monday

2. SELECT (datepart(ww,@tDate)) + 1- datepart(ww,dateadd(dd,-datepart(dd,@tDate),@tDate)) nWeek //Week number in a month for a specific date

3. SELECT DATEADD(mm, DATEDIFF(mm,0,@Month), 0) //First Day of the Month

4. SELECT DATEPART(WK, @Month) //Week Number of the Year

5. Select Current_Timestamp; // return current Select Current_Timestamp;

6. By using any of the following date functions it is possible to breakdown timestamps


SELECT MONTH(CURRENT_TIMESTAMP);- Return a Month(6)

SELECT DAY(CURRENT_TIMESTAMP);-Return a Day:(22)

SELECT DATE(CURRENT_TIMESTAMP); - returns a date (2004-06-22)

SELECT TIME(CURRENT_TIMESTAMP); - returns the time (10:33:11.840)

SELECT DAYOFWEEK(CURRENT_TIMESTAMP); - returns a numeric value (1-7)

SELECT DAYOFMONTH(CURRENT_TIMESTAMP); - returns a day of month (1-31)

SELECT DAYOFYEAR(CURRENT_TIMESTAMP); - returns the day of the year (1-365)

SELECT MONTHNAME(CURRENT_TIMESTAMP); - returns the month name (January - December)

SELECT DAYNAME(CURRENT_TIMESTAMP); - returns the name of the day (Sunday - Saturday)

SELECT WEEK(CURRENT_TIMESTAMP); - returns number of the week (1-53)