Date and Time functions 101

A short reference document to some of the Date / Time functions in SQL Server.

A short introduction to Date / Time functions

Often I'm asked the question "How do I achieve this specific goal using the Date and Time functions of SQL Server", in an attempt to minimise the number of questions with regard to these functions I'm writing this short introduction to the most usefull Date / Time functions.

In this article I will be dealing with the following Functions:

DATEADD Returns a new datetime value based on adding a interval to the specified date
DATEDIFF Returns the number of date and time boundaries crossed between two specified dates
DATENAME Returns a character string representing the specified datepart of the specified date
DATEPART Returns a integer representing the specified datepart of the specified date
DAY Returns a integer representing the day part of the specified date
GETDATE Returns the current system date and time in the MS SQL Server standard internal format for datetime values
MONTH Returns a integer that represents the month part of a specified date
YEAR Returns a integer that represents the year part of the specified date

Arguments for Dateadd, Datediff, Datename, Datepart:

Datepart Abbreviations
Year yy, yyyy
Quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
Minute mi, n
Second ss, s
Millisecond ms

 

DATEADD

Syntax:

DATEADD ( datepart, number, date )

Usage:

DATEADD is used to add values to your existing datetime field, for instance in my example I'm pulling out my Upload dates and in a second field I will select the Upload dates incremented by 10 days:

SELECT docUploadDatum, DATEADD(day, 10, docUploadDatum) AS docUploadDatumADDED
FROM FISDocumenten

Result:

Adding 10 days to the UploadDate field

As you can see in the screenshot, this query returns both the date and the date after we run the add 10 days Function on our date, this functions works the same with months and years but also with hours, minutes, seconds and milliseconds. This is the function to use when we want to display a date on a invoice page, for instance, that has to be 30 days later then the purchase date, if we had a 30 day payment rule.

DATEDIFF

Syntax:

DATEDIFF ( datepart , startdate , enddate )

Usage:

This function returns the difference specified in the datepart format you specify, for instance using the same example I can determine using DATEDIFF how long ago a file was uploaded into the system, an example:

SELECT docUploadDatum, DATEDIFF(day, docUploadDatum, getDate()) AS docUploadDaysAgo
FROM FISDocumenten

Result:

Determining how many days ago files were uploaded using DateDiff

As you can see in the screenshot, the first document uploaded into the system was 242 days ago today, tomorrow this same query will inform me that the same document was uploaded 243 days ago and offcourse that is correct. This Function can be usefull for Human Resource Departments that need to monitor when a employee is 5, 12.5 or 25 years in service, use this in a stored procedure that will send out a mail when a employee is the nr of months -1 in service and Human Resources will have a month advance notice, Handy !

DATENAME

Syntax:

DATENAME ( datepart , date )

Usage:

With DateName we select a datepart that we want returned, for instance if we use datepart "day" SQL will return to us the day numeric value in our query, in the example below I have used the argument "dw" which stands for Day Of Week and that returns the written day:

SELECT docUploadDatum, DATENAME(dw, docUploadDatum) AS docUploadDay
FROM FISDocumenten

Result:

DateName returns the Day our documents are uploaded. (Excuse my Dutch server)

Nice, Donderdag = Thursday, dinsdag = Tuesday and Woensdag = Wednesday, for the english among us.

Now, what would we use this for ? One example would be if we only wanted to return items that have a rule "only show up at this weekday" in our system, we would use the DateName as a Filter as in this second example:

SELECT docUploadDatum, DATENAME(dw, docUploadDatum) AS docUploadDay
FROM FISDocumenten
WHERE DATENAME(dw, docUploadDatum) = 'woensdag'

Result:

Now only results from wednesday are returned.

Part 2

Dennis van Galen

Dennis van GalenDennis started his career as order picker in warehouses. In the past 10 years he did alot of data-entry work for Government agencies and around the age of 20 he helped clean the KPN Telecom customer databases. At the age of 27 Dennis returned to KPN Telecom where he was a full time webmaster / webdeveloper. In his spare time he used to be a voluntary Manager for DMXzone.com. After leaving KPN in 2012 Dennis worked for Tevreden.nl on webbased customer satisfaction platforms.

In the past 12 years Dennis became experienced with various webtools, web-languages and database systems.

See All Postings From Dennis van Galen >>

Comments

Be the first to write a comment

You must me logged in to write a comment.