Date & Time functions in MSSQL query

There are various functions available in MSSQL database to handle date and time. We can perform date and time calculations through SQL and display the result. There are many date functions for data handling and we will discuss here with some examples. These functions can be combined with various other functions of ASP to develop powerful scripts.

DateAdd: Adding data to DateTime field
We can add data to DateTime field of MSSQL database by using Query or by using default constraint to the table. We can add future date also.

Date Format
We can format date and time to store them in MSSQL database. Before adding we can check the format also.

Parts of the Date
Getting different day parts like Year, Month, day etc from the MSSQL table

Day Function of MSSQL DateTime field

We can collect the day part from the datatime field by using day function in MSSQL database

select day(issue_dt) as day, issue_dt from dt

The output of this query is here.

day	issue_dt
28	12/28/2007 7:54:56 PM
31	10/31/2010 7:23:47 PM
31	10/31/2010 7:30:14 PM
31	10/31/2010 7:30:20 PM
28	2/28/2010 7:54:56 PM

Month Function of MSSQL DateTime field

We can get the month part in number 1 to 12 by using Month function in MSSQL. Here is the query to get Month part along with the full date.

select     Month(issue_dt) as month, issue_dt  from dt

The output of this query is here.

month	issue_dt
12	12/28/2007 7:54:56 PM
10	10/31/2010 7:23:47 PM
10	10/31/2010 7:30:14 PM
10	10/31/2010 7:30:20 PM
2	2/28/2010 7:54:56 PM

As you have seen we have shown month along with the full date, now we will try to list out number of issues in every month by using Group by command. We will display Total number along with the month number. Here is the query.

rs1.open " select   COUNT(issue_dt) as no,  Month(issue_dt) as isdt  from dt  GROUP BY Month(issue_dt)

Month	isdt
2	1
10	3
12	1

The left column is the month and next column shows number of issues against that month. You can check from the first display list the numbers to the second display list

Year Function of MSSQL DateTime field

Year we can read from MSSQL datetime field by using year function . Query is here

select     Year(issue_dt) as Year, issue_dt  from dt

The output is here

Year	issue_dt
2007	12/28/2007 7:54:56 PM
2010	10/31/2010 7:23:47 PM
2010	10/31/2010 7:30:14 PM
2010	10/31/2010 7:30:20 PM
2010	2/28/2010 7:54:56 PM

We have seen how month function is used along with group by function. Let us try to list out number of issues in the Year 2010 against months by using group by command and where clause. Here is the query

select   COUNT(issue_dt) as no,  Month(issue_dt) as isdt  from dt WHERE Year(issue_dt)='2010' GROUP BY Month(issue_dt)

isdt	no
2	1
10	3

The left column is the month and next column shows number of issues against that month for the year 2010.

Example of Date Queries in MSSQL database

We can collect records based on various functions based on date field. Date Query examples

Getting records of today

select * from dt where issue_dt = GETDATE()

In our library issue table we have a issue date, so we can use this to list all books issued today Let us find out number of days passed since the issue date for all the books

select datediff(dd,issue_dt,GETDATE())as diff, issue_dt from dt

We will try to list this in order of highest number of days to lowest number of days by using order by

select datediff(dd,issue_dt,GETDATE())as diff, issue_dt from dt order by diff desc

Last 15 days record

We can get the recods of last 15 days from current day like this.

select * from dt  where datediff(dd,issue_dt,GETDATE()) < 15

We can change the above query and get desired result in Month or Years .

Difference in month year and days using DateDiff in MSSQL query

We can use DateDiff function in MSSQL query to get difference in dates between two date and time fields. Here we will discuss how to use DateDiff inside a MSSQL query.

DateDiff(return type , date1, date2)

Here we can specify the return type like month , days, year, weeks etc. Date1 and date2 are two dates between which the difference is to be calculated.

We will use the same table what we have used in update date and time field example. So let us find out the query required to collect difference in days between two dates.

rs1.open " select   book_id,DateDiff(mm,issue_dt,return_dt) as dif, issue_dt, return_dt from dt   " , conn

Using the above query we can find out difference in days. The rest of the code remains same for any other record display script. We will display the total script at the end of this tutorial.

Now let us go for finding out difference in years

rs1.open " select   book_id,DateDiff(yyyy,issue_dt,return_dt) as dif, issue_dt, return_dt from dt   " , conn

You can see the difference in formatting the DateDiff function in above SQL. See the list of different formats for DateDiff here. Here is the total code.

<%
Dim conn,rs,rs1,SQL,RecsAffected,qr,bgcolor

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(yyyy,issue_dt,return_dt) as dif, issue_dt, return_dt from dt   " , conn

Response.Write "<table>"

Do While Not rs1.EOF 
if(bgcolor="#f1f1f1") then 
bgcolor="#ffffff"
Else
bgcolor="#f1f1f1"
End if 

Response.Write "<tr bgcolor=" & bgcolor & "><td> " & rs1("book_id") & " </td><td> " & rs1("issue_dt") & " </td><td> " & rs1("return_dt") & " </td><td> " & rs1("dif") & "</td></tr> "

rs1.MoveNext
Loop

Response.Write "</table>"
Set rs1 = Nothing
conn.Close
Set conn = Nothing
%>

Table structure is here

CREATE TABLE [dbo].[dt] (
	[book_id] [int] IDENTITY (1, 1) NOT NULL ,
	[issue_dt] [datetime] NOT NULL ,
	[return_dt] [datetime] NOT NULL 
) ON [PRIMARY]
GO

Table data is here

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

Date & time field data updating

We will try to read a date and time field and populate the data in a text box. Then we can ask the users to edit the date and time value and update the record. Here we are asking users to change the data which has to be stored in a date and time field, so we have to check the entered data is a valid date and time or not. We will be using the IsDate to validate the date and time data entered by user. One if condition checking we will do and it is passed ( true ) then only the query to update the record will be executed, else the corresponding error message will be displayed.

Here we are using one table name dt which has three columns ( fields ), book_id one auto MSSQL increment identity column , issue_dt one date and time field & return_dt one date and time field ( note that date and time fields here are not short date and time field.) . We will try to collect a record with book_id = 5 and update the same record. You can download the table structure with data at the end of this page.

<%
Dim conn,rs,SQL,dtm,dtm2,dtt,todo

Set conn=Server.CreateObject("ADODB.Connection")
conn.Mode=adModeRead
conn.ConnectionString = aConnectionString
conn.Open
Set rs =Server.CreateObject("ADODB.Recordset")

todo=Request("todo")
if(todo="update") Then
dtt=Request("dtt")
Response.Write dtt
if isDate(dtt) Then 
rs.open " update dt set  issue_dt='" & dtt & "'  where book_id=5   " , conn
Else
Response.Write " Wrong Date format "
End if
End if 

'Displaying data from the table.

rs.open " select  book_id, issue_dt from dt where book_id=5   " , conn
if rs.EOF Then
Response.Write "No Record exist"
Else
Response.Write "<form method=post action='' ><input type=hidden name=todo value=update><input type=text name =dtt value= '" & rs("issue_dt") & "'><input type=submit value=Update></form>"
Response.Write "After the update <br>Book Id:  " & rs("book_id") &" Issude Date: " & rs("issue_dt")
End if 

Set rs = Nothing
conn.Close
Set conn = Nothing
%>