SQL Server date and time functions

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

SELECT 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

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

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

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

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

CREATE 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

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: