SQL Date Functions
Introduction to SQL Server’s Date Functions
SQL server’s date functions provide you a set of function that you can use to manipulate dates. The function are used for a wide variety of operation such as adding weeks to a date, calculating the difference between two dates, or to decompose a date into its fundamental parts.
If you not familiar with SQL functions, then I would recommend staring with the Introduction to SQL Server Built-In Functions.
To get the most of this and our other lessons be sure to practice using the examples!
There are over twenty five different functions categorized as date functions in SQL server.
All of the functions are listed on the Date Functions (Transact-SQL) page. I would recommend visiting that page to learn about each function.
Rather than reiterate that material, we’ll focus on the functions I’ve seen in commonly used in business.
In the following tables I categorized the functions and color coded them. The color code corresponds to the likely hood you would use that particular function in a business environment. Green are most likely to be used, and red less.
This isn’t a strict scale, and all functions have a use in some business case, but I wanted a way to help you winnow down the field to those most relevant.
Here is my attempt:
Latest Posts
-
SQL YEAR Function (Transact SQL)
You can use the SQL Year function to return just the year portion of a date. The value returned is an integer. The YEAR function works the same as the DATEPART (year, date) and returns the year part of the specified date. SQL YEAR Usage Notes The only argument for the YEAR function is the…
-
SQL DATEPART Function (Transact SQL)
The SQL DATEPART function returns the specified part of the input date. Description The SQL DATEPART function returns an integer value that indicates the part of the date specified by the user. The interval to be retrieved can be a date, year, hour, minute, etc. Returns an integer which represents the specified part of a…
-
SQL GETDATE Function (Transact SQL)
The SQL GETDATE function returns the current timestamp of the database as a datetime value. Description The current timestamp derives from the operating system on which the SQL server is running. The GetDATE function returns the timestamp in the format YYYY-MM-DD hh:mm:ss:mmm. SQL GETDATE Usage Notes The return data type of the GETDATE function is…
-
SQL DATEADD Function (Transact SQL)
Use SQL DATEADD to add days, weeks, months, or any timespan specified by datepart to a date to get another. The function returns the modified date. Description The function adds or deletes a specified time period from the date value. The period value can be a year, month, day, week, hour, minute, second, etc. A…
-
SQL DATEDIFF Function (Transact SQL)
The SQL DATEDIFF function calculates and returns the difference between two date values. The value returned is an integer. You can use DATEDIFF to calculate a wide variety of calendar calculation by varying the datepart parameter. Description Use SQL DATEDIFF to return the difference between the two dates based on a specified date part. The…
-
Calculate the Last Day of the Month using SQL
When working with SQL dates, sometimes you need to calculate the end of the month. Months are tricky! Some are 28 days, others 30 or 31, and now and then there’s a leap year! So, given a date, how do you calculate the number of days remaining in the month? The calculation is really a…