In this article we will discuss various date related
functions available in SQL, which are very useful when we need to perform some
action on given date.
Get Date & Time:
Select
GETDATE() as DateTimeNow
Result:
2020-03-25 12:02:07.930
|
To get date you can simply use above functions, it will
give you current date & time both. You can use GETDATE() to get datetime in procedure or function. You
can pass GETDATE() As a value to parameter.
For Ex.
Insert
into
Table_Name(OrderNo,Quantity,
OrderDate)
values ('March-005', 350, GETDATE())
|
Get Only Date:
SELECT
CONVERT(Date, GETDATE())
Result: 2020-03-25
|
Commonly Used Date functions:
1. DATEPART()
2. DATEADD()
3. DATEDIFF()
1.
DATEPART():
Use to get any Part of
given Date ex Day, Month or Year.:
Syntax:
DATEPART(datepart, datecolumn)
For Ex:
Select
DATEPART(DAY, GETDATE())
as
CurrentDay
CurrentDay:
25
|
2. DATEADD():
Functions is used when we want to get date by adding
days or month or year in given date. It will display the date and time by
adding or subtracting datepart.
Syntax :
DATEADD(DATEPART, number, datecolumn)
(You need to mention datepart here i.e.
Days, month or year.)
For ex.
Select
DATEADD(DAY, 5, '2013-03-25')
as
NewDatetime
NewDatetime: 2013-03-30 00:00:00.000
|
3. DATEDIFF():
use to get Difference between Two Dates.
This
function will return difference between two dates in any of the following
datepart: Hours, days,Months &
years.
Syntax:
DATEDIFF
(datepart, startdate, enddate)
For Ex.
Select
DATEDIFF( HOUR,
'2020-03-25' , '2020-03-26')
as
HourDifference
HourDifference:
24
|