Back to Top
The nuts and bolts for
Creative web development

Date and Time functions 101

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

DATEPART

Syntax:

DATEPART ( datepart , date )

Usage:

DATEPART is used primarily to select a PART of the date, for instance a day, month or year and then use it in a filter or something similar, let us select the month from our UploadDate:

SELECT docUploadDatum, DATEPART(mm, docUploadDatum) AS docUploadMonth
FROM FISDocumenten

Result:

The month selected as a numerical value.

I have written another article on the DatePart function "How to sort using DatePart" which is found here.

DAY

Syntax:

DAY ( date )

Usage:

The function DAY does essentially the same as using DATEPART(dd, date). It selects the Day value from existing dates, let us do a quick example and move on since this function is not so special or difficult to use:

SELECT docUploadDatum, DAY(docUploadDatum) AS docUploadDay
FROM FISDocumenten

Result:

Upload days are returned by simply specifying DAY(date).

This returns the integer values representing the days that files were uploaded.

GETDATE

Syntax:

GETDATE()

Usage:

Getdate() is SQL Server's equivalent of the MS-Access Function Date(), it simply returns the system date. This Function is usually used to define a default date in a table so that newly inserted records automatically receive the date which removes the need to have a invisible field in your web-form that inserts the date, SQL Server can do this by default, just make sure you specify it. Other popular uses of getDate() is to filter using system dates, an example of filtering:

SELECT *
FROM FMIdraaiboek
WHERE Datum >= getDate() AND Datum < getDate()+30

Results:

Using getDate to select a production schedule of the next 30 days

Ok, what just happened here ? Well, first I told SQL to select everything ( * ) from table FMIdraaiboek (a release date table). Then we tell SQL that we want ALL entries that have a Date HIGHER or Equal to today AND a date lower then today+30 days, resulting in a 30 day release schedule. That is just one alternate way of using getDate to automate your applications, this query really produces a new recordset every day, so it automated my release schedule. Just make sure the table doesn't run out of data or you could find yourself ending up with a BOF/EOF error on your production page, so to be safe add show/hide regions that inform the user that there are no further dates in the current production schedule.

MONTH

Syntax:

MONTH ( date )

Usage:

The function MONTH does essentially the same as using DATEPART(mm, date). It selects the Month value from existing dates, let us do a quick example and move on since this function is not so special or difficult to use:

SELECT docUploadDatum, MONTH(docUploadDatum) AS docUploadMonth
FROM FISDocumenten

Result:

Upload months are returned by simply specifying MONTH(date).

This returns the integer values representing the months that files were uploaded.

YEAR

Syntax:

YEAR ( date )

Usage:

The function YEAR does essentially the same as using DATEPART(yyyy, date). It selects the Year value from existing dates, let us do a quick example and move on since this function is not so special or difficult to use:

SELECT docUploadDatum, YEAR(docUploadDatum) AS docUploadYear
FROM FISDocumenten

Result:

Upload years are returned by simply specifying YEAR(date).

This returns the integer values representing the years that files were uploaded.

And that's all I've got time for in this article, in an upcoming article I will go into some of the aggregate functions.

Part 1

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 >>

Share this Article