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.
We can defined user defined function too, to know more about user defined function in sql see on EnzoBlog.