Date and Time functions in SQL Server

There are some many scenarios in application when we required to calculation on dates like if you want to know specific day name from a given date then you need to use some inbuild SQL functions.

For example if you want to calculate age on any employee based on date of birth saved in database then we need to do calculation on date, if you want to know the previous months last day then you need to do some calculation on given date, In short we can say that SQL has many date and time functions and different formats that we are going to discuss in this article . Below is few functions that is used in database oprations on dates.

• GETDATE() :- It will return the current date with time.
• SYSDATETIME() :- It will return the system current date with time.
• GETUTCDATE() :- It will return the UTC current date with time.
• ISDATE() :- It will return an boolean value and used for checking the given date in parameter and it returns true or false based on the date given, if date is correct then it will return 1 otherwise 0.
• DAY() :- It is a SQL inbuild function in which we need to pass date as a parameter and it will return a day number from the date. Day number means number of that particular day in a week.
  Syntax :- Select DAY('2023-10-03') output you can see in below images.
• MONTH() :- It is a SQL inbuild function in which we need to pass date as a parameter and it will return a Month number from the date. Month number means number of that particular Month in a Year.
  Syntax :- Select Month('2023-10-03') output you can see in below images.
• YEAR() :- It is a SQL inbuild function in which we need to pass date as a parameter and it will return a Year number from the date. Year number means number of that Year.
  Syntax :- Select YEAR('2023-10-03') output you can see in below images.
• DATENAME() :- It is a SQL inbuild function in which we need to pass date and (DAY,Month,Year) as a parameter and it will return a DAY number, a month name, a year from the date. .
  Syntax :-
  select DATENAME(DAY,'02-02-2024') DAY as an input parameter with date
  select DATENAME(MONTH,'02-02-2024') Month as an input parameter with date
  select DATENAME(YEAR,'02-02-2024') Year as an input parameter with date
  select DATENAME(weekday,'02-05-2024') Weekday as an input parameter with date
  select DATENAME(dayofyear,'02-05-2024') dayofyear as an input parameter with date
  select DATENAME(week,'02-05-2024') week as an input parameter with date
  select DATENAME(hour,'02-05-2024 12:48') hour as an input parameter with date
  outputs of above query syntax you can see in below images.
• DATEPART() :- It is a SQL inbuild function in which we need to pass date and (DAY,weekday,dayofyear,week,hour) as a parameter and it will return a DAY number, a week number,day of year,week number in year and hour from the date. .
  Syntax :-
  select datepart(day,'09-23-2023') DAY as an input parameter with date
  select datepart(weekday,'09-23-2023') weekday as an input parameter with date
  select datepart(dayofyear,'09-23-2023') dayofyear as an input parameter with date
  select datepart(week,'09-23-2023') week as an input parameter with date
  select datepart(hour,'09-23-2023 4:34') hour as an input parameter with date
  outputs of above query syntax you can see in below images.
• DATEADD() :- It is used for adding a speicific number of days, months and years into the given date and will calculate the added date.
  Basic Syntax :-
  select DATEADD(day,20,'04-05-2023') 20 days as an input parameter with date
  select DATEADD(month,5,'04-05-2023') 5 months as an input parameter with date
  select DATEADD(YEAR,5,'04-05-2023') 5 years as an input parameter with date .
  outputs of above query syntax you can see in below images.
• DATEDIFF() :- It is used for getting the difference between two dates.
  Basic Syntax :-
  select DATEDIFF(month,'2024-02-03','2024-05-05') It will return number of months difference between two given dates in parameters.
  select datediff(day,'2024-02-03','2024-05-05')It will return number of days difference between two given dates in parameters.
  select DATEDIFF(year,'2024-02-03','1982-09-04') It will return number of years difference between two given dates in parameters.
  outputs of above query syntax you can see in below images.
• EOMONTH() :- It will return the last day of month or last day of specified month given in condition.
  Syntax:-
  select EOMONTH('05-02-2024',-1) It will return last day of previous month from given dates in parameters.