Inserting present or default date time in MSSQL table by DateAdd & GetDate using ASP

WE can store current date and time in one MSSQL field by using GETDATE() function. Here we can design our table in such a way that while adding a record one field stores the current date and time. We can also use various ASP date functions like Now to first collect date value and then add the value to MSSQL table.

Using ASP & Now function with MSSQL Insert Query

Let us start with the ASP solution where we will find out the present date and time and then add that value while inserting a record. We will use the example of a simple library issue system where we have to add book_id, issue date and return date in a record. To add the today's date and time we will use now function and store it in a variable. To find out the return date we will use DateAdd function to get the return date by adding 15 days to the present date ( or issue date ). We will store the current date as issue date and 15 days advance date and time as return date. We are also including the time for a better understanding of date and time field. Here is the code we will be using, we are not discussing the connecting string and record set part as you can get those details in any previous tutorials.

Dim dtm,dtm2
dtm=Now()
dtm2=DateAdd("d",15,dtm)
rs.open "insert into dt(book_id,issue_dt,return_dt) values( 9,'"& dtm &"','"& dtm2 &"')", conn

Using DEFAULT CONSTRAINTS in MSSQL table

We can set one default constraint to MSSQL table by using getdate() function. Here while inserting a record we will not add value to our this field through query and MSSQL will find out the current date and time and add that to our filed. In the above example we were adding records to three fields ( book_id, issue_dt , return_dt ) but there are five fields in total. The first field issue_id is one identity field so no need to specify its value, one more field dtt is kept to store default date and time value by using default constraints. See the sql script for the table and read how the default value for this field is created.

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

ALTER TABLE [dbo].[dt] WITH NOCHECK ADD 
	CONSTRAINT [DF_dt_dtt] DEFAULT (getdate()) FOR [dtt]

In the above code you can see the value of dtt field is taken from the function getdate and stored. So each time a record is added the field dtt gets the current date and time value.

Adding advance date as default date with DateAdd

We can insert any advance date using DateAdd function along with GetDate function as advance date in MSSQL . We will try to build a constraints by adding 15 days to present date to the current date. The default value should look like this .

(dateadd(day,15,getdate()))

Now with this default constraints our table structures is here.

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

ALTER TABLE [dbo].[dt] WITH NOCHECK ADD 
	CONSTRAINT [DF_dt_issue_dt] DEFAULT (getdate()) FOR [issue_dt],
	CONSTRAINT [DF_dt_return_dt] DEFAULT (dateadd(day,15,getdate())) FOR [return_dt],
	CONSTRAINT [DF_dt_dtt] DEFAULT (getdate()) FOR [dtt]
GO

Date Format Date Parts
ASP Home

Inserting present and advance date and time in MSSQL table by using ASP or CONSTRAINT

Using ASP & Now function with MSSQL Insert Query

Using DEFAULT CONSTRAINTS in MSSQL table

Adding advance date as default date with DateAdd