Learn working of Date function in SQL

In programming, situation usually comes to manage data with dates. Sql inbuilt function provides help to manipulate the data to avoid date errors in different scenario.

Following are the commonly used inbuilt Date function in sql server.

1. GetDate()
2. Convert()
3. IsDate()
4. DatePart()
5. DateADD()
6. DateDiff().
7. DateName()
8. Day()
9. Month()
10. Year()

In this section we will describe use of above functions in detail

1. GetDate() :
GetDate() will return current date and time

select getdate() as Transaction_D

Output
Transaction_D
-----------------------
2015-05-26 11:23:15.313

2. Convert() :
In many case situations arise in formation of a date like “DD/MM/YYYY”, “MM/DD/YYYY”.
Following table show standard date time format are often used in different scenario.

By Default Sql Server use YYYY-MM-DD Format for date type data. The above sql statements are displaying output with current date and time in given date format.

The date in these statements should be date type data.

3. ISDate() :

ISDATE () return 1 in case of valid date and time value otherwise it return 0. ISDate() could be use for date validation. In case of 1 we can write further business code otherwise we can print a message for date failure.

declare
@paramdate varchar(20) ='2015-05-26'
IF ISDATE(@paramdate) = 1
PRINT 'This is valid date'

declare
@paramdate varchar(20) ='Manoj'
IF ISDATE(@paramdate) = 0
PRINT 'please enter valid date'

4. DATEPART()
DATEPART () function Return date part in a number from a specified date.
Part could be day/week/month/year/hour/minute/second from a date and time value.

select datepart(day,'2015-06-26')  -- will return day of the date in integer 
Result: 26

select datepart(month,'2015-06-26') --will return month of the date in integer
Result: 6

select datepart(year,'2015-06-26') -- will return year of the date in integer
Result: 2015

select datepart(hour,'2015-06-26 12:37:16.307') -- will return hour of specified date
Result: 12

5. DateAdd()

It Returns a date with the specified number interval (In Number) added to a given DATEPART (day/week/month/year/hour/minute/second) of that date.

select dateadd(day,1,'2015-06-26 12:37:16.307')  -- will return date with 1 day after specified date
   Result: 2015-06-27 12:37:16.307

   select dateadd(month,1,'2015-06-26 12:37:16.307') -- will return date with 1 month after specified date
   Result: 2015-07-26 12:37:16.307

   select dateadd(year,1,'2015-06-26 12:37:16.307') -- will return date with 1 year after specified date
   Result: 2016-06-26 12:37:16.307

In above sql statement I have increased day, month and year with 1 interval.

6. DateDiff():
Date Diff return the date time difference between two specified dates

select datediff(day,'2015-06-25', '2015-06-26') -- showing day difference between 25th and 26th 
Result: 1

select datediff(month,'2015-04-01', '2015-06-01') -- showing month difference between April 2015 and June 2015
Result: 2

select datediff(year,'2012-04-01', '2015-06-01') -- showing year difference between April 2013 and June 2015
Result: 3

7. DateName() :
Date name represent the character string of specified date of given date part

SELECT DATENAME(weekday,'2015-06-26'); -- represent week name
Result: Tuesday

SELECT DATENAME(month,'2015-06-26'); -- represent month name 
Result: June

8. Day() :
Day function return day from the specified date.

select day('2015-05-26')
Result: 26

9. Month() :
Month function return month from specified date

select month('2015-05-26')
Result: 5

10. Year () :
Year function return year from specified date.

select year('2015-05-26')
Result: 2015

Hence we have learned to working with date in sql.

About the Author

Tutor @ Eduonix

Administrator

View All Posts