SQL Server date and time functions

Use this example to see the core idea, then work through the complete steps below. Code snippets are Classic ASP / VBScript or SQL Server T-SQL as labelled, not PHP.

Extract date components

SQL example

 CopySELECT DAY(issue_dt) AS issue_day,
       MONTH(issue_dt) AS issue_month,
       YEAR(issue_dt) AS issue_year
FROM dbo.dt;

This example uses the dbo.dt practice table used across the SQL date lessons.

SQL Server provides date/time functions for extracting parts, calculating intervals, filtering ranges and creating default timestamps. These examples preserve the original dt practice table while updating the queries and ASP data handling.

DAY()

SELECT DAY(issue_dt) AS issue_day, issue_dt
FROM dbo.dt
ORDER BY issue_dt;

MONTH()

SELECT MONTH(issue_dt) AS issue_month, issue_dt
FROM dbo.dt
ORDER BY issue_dt;

Count rows by month

rs1.Open "SELECT MONTH(issue_dt) AS issue_month, COUNT(*) AS issue_count FROM dbo.dt GROUP BY MONTH(issue_dt) ORDER BY issue_month", conn

If the table spans multiple years, group by both year and month when you need separate monthly totals for each year.

YEAR()

SELECT YEAR(issue_dt) AS issue_year, issue_dt
FROM dbo.dt
ORDER BY issue_dt;

Count 2010 rows by month

 CopySELECT MONTH(issue_dt) AS issue_month, COUNT(*) AS issue_count
FROM dbo.dt
WHERE issue_dt >= '20100101' AND issue_dt < '20110101'
GROUP BY MONTH(issue_dt)
ORDER BY issue_month;

The range predicate keeps the date column unwrapped, which is generally more index-friendly than filtering with YEAR(issue_dt)=2010.

Get today's rows

SELECT *
FROM dbo.dt
WHERE issue_dt >= CAST(GETDATE() AS date)
  AND issue_dt < DATEADD(day, 1, CAST(GETDATE() AS date));

Comparing a timestamp directly to GETDATE() normally fails to find “today's” rows because the times must match exactly. The half-open range covers the complete current date.

Days since issue

SELECT DATEDIFF(day, issue_dt, GETDATE()) AS days_since_issue, issue_dt
FROM dbo.dt;

DATEDIFF() counts datepart boundaries crossed between two values; it is not a fractional elapsed-time calculator.

Order by the date difference

SELECT DATEDIFF(day, issue_dt, GETDATE()) AS days_since_issue, issue_dt
FROM dbo.dt
ORDER BY days_since_issue DESC;

Rows from the last 15 days

 CopySELECT *
FROM dbo.dt
WHERE issue_dt >= DATEADD(day, -15, GETDATE())
  AND issue_dt <= GETDATE()
ORDER BY issue_dt DESC;

A direct date-range predicate is clearer than applying DATEDIFF() to every row and also avoids accidentally including future dates.

DATEDIFF() syntax

DATEDIFF(datepart, startdate, enddate)

Common dateparts include day, month, year, hour, minute and second.

Difference in months

rs1.Open "SELECT book_id, DATEDIFF(month, issue_dt, return_dt) AS month_diff, issue_dt, return_dt FROM dbo.dt ORDER BY book_id", conn

Difference in years

rs1.Open "SELECT book_id, DATEDIFF(year, issue_dt, return_dt) AS year_diff, issue_dt, return_dt FROM dbo.dt ORDER BY book_id", conn

Complete ASP display example

 Copy<%
Dim conn, rs1
Set conn = Server.CreateObject("ADODB.Connection")
conn.Mode = adModeRead
conn.ConnectionString = aConnectionString
conn.Open
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open "SELECT book_id, DATEDIFF(year, issue_dt, return_dt) AS year_diff, issue_dt, return_dt FROM dbo.dt ORDER BY book_id", conn
Response.Write "<table class='table table-striped'>"
Do While Not rs1.EOF
Response.Write "<tr><td>" & Server.HTMLEncode(CStr(rs1("book_id"))) & "</td><td>" & _
Server.HTMLEncode(CStr(rs1("issue_dt"))) & "</td><td>" & _
Server.HTMLEncode(CStr(rs1("return_dt"))) & "</td><td>" & _
Server.HTMLEncode(CStr(rs1("year_diff"))) & "</td></tr>"
rs1.MoveNext
Loop
Response.Write "</table>"
rs1.Close
Set rs1 = Nothing
conn.Close
Set conn = Nothing
%>

Practice table

 CopyCREATE TABLE dbo.dt (
  book_id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_dt PRIMARY KEY,
  issue_dt DATETIME2(0) NOT NULL,
  return_dt DATETIME2(0) NOT NULL
);

Sample rows:

3,2007-08-23 00:00:00,2007-09-25 00:00:00
4,2007-12-15 00:00:00,2008-01-02 00:00:00
5,2007-07-05 19:59:56,2007-07-20 19:54:56

Update a date/time value from a form

Validate the request value with IsDate(), convert it to a date value, then pass it as a parameter. Do not concatenate the visitor-entered date into SQL.

 Copy<%
Dim cmd, dtt, todo
todo = Request.Form("todo")

If todo = "update" Then
dtt = Request.Form("dtt")
  If IsDate(dtt) Then
Set cmd = Server.CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "UPDATE dbo.dt SET issue_dt=? WHERE book_id=?"
cmd.Parameters.Append cmd.CreateParameter("pDate", adDBTimeStamp, adParamInput, , CDate(dtt))
    cmd.Parameters.Append cmd.CreateParameter("pBook", adInteger, adParamInput, , 5)
    cmd.Execute
Set cmd = Nothing
Else
Response.Write "Please enter a valid date and time."
End If
End If
%>

When redisplaying the current value in a form control, HTML-encode it before placing it in markup.

Practice table structure for the update example

 CopyCREATE TABLE dbo.dt (
  book_id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_dt PRIMARY KEY,
  issue_dt DATETIME2(0) NOT NULL,
  return_dt DATETIME2(0) NOT NULL
);

Sample rows:

3,2007-08-23 00:00:00,2007-09-25 00:00:00
4,2007-12-15 00:00:00,2008-01-02 00:00:00
5,2007-07-05 19:59:56,2007-07-20 19:54:56

Prefer date ranges over extracting parts in a WHERE clause

For indexed timestamp columns, a half-open range such as issue_dt >= @start AND issue_dt < @end often works better than wrapping the column in YEAR() or CONVERT(). It also safely includes every time within the last day.

DATEDIFF counts boundaries

DATEDIFF(day, start, end) counts crossed day boundaries; it is not a rounded duration in 24-hour units. Use DATEADD for interval arithmetic. See DATEPART() for retrieving individual components.

Frequently asked questions

Are these queries for SQL Server or Access?

The SQL pages use SQL Server T-SQL. Access has different date functions, identity behavior, and SQL syntax.

Can I execute these statements directly on a live database?

Start in a test database. INSERT, UPDATE, DELETE, DROP and CREATE change data or schema.

How should Classic ASP send input to a query?

Use an ADODB.Command with typed, ordered parameters and encode database values when writing HTML.

Back to top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: