Date and Time functions 101

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:

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:

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:

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:

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:

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