Insert DateTime into SQL Database

[boux123]

Hi,

I creating this form where one of the fields shows the current date and time (read only). i want to insert this current date into my SQL database table under a certain column (varchar type). I didnt use any of the date or time types because their size is standard. When I created test forms and submitted it to my database. The current date and time is display in the table like this: 1/1/1900 12:00:00 AM. Im assuming this is the default dateTime. Im not sure how to fix this problem.

Here is my code:

This is my insert statement:

oConn.Open(ConnectionString)
queryStr = "INSERT INTO tblPDRitem ( pdrItemCreationTime, packageName, packageType, actionRequested, distributionServers) VALUES('" & currentDateEntry & "','" & TRIM(Request.QueryString("packageName")) & "', '" & TRIM(Request.QueryString("packageType")) & "','" & TRIM(Request.QueryString("actionRequested")) & "','" & TRIM(list) & "')"
Set oRsSub = oConn.Execute(queryStr)
oConn.Close

This is where I input the date in the form:

<input type = "text" readonly name= "currentDate" value="<%= FormatDateTime(Now) %>" />

Thx

 

[gmmastros]

This is SQL Server, right?

I recommend you use the SQL server function GetDate() This will enter the date/time in to the column based on the server's date/time.

Also, you should change the data type to DateTime. It takes less storage AND gives you more functionality. After you change the data type, your query could be...

oConn.Open(ConnectionString)    
       queryStr = "INSERT INTO tblPDRitem ( pdrItemCreationTime, packageName, packageType, actionRequested, distributionServers) VALUES([!]GetDate()[/!],'" & TRIM(Request.QueryString("packageName")) & "', '" & TRIM(Request.QueryString("packageType")) & "','" & TRIM(Request.QueryString("actionRequested"))  & "','" & TRIM(list) & "')"
    Set oRsSub = oConn.Execute(queryStr)
    oConn.Close

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rac2]

Do you have a line of code like this somewhere before executing the INSERT ?

currentDateEntry = Request.QueryString("currentDate")

If not, this would account for what you are seeing. If currentDateEntry does not have a value assigned then it will be handled like an empty string. SQL Server will convert that to the beginning of time, the zero-date, Jan 1, 1900 at midnight.

 

[DotNetGnat]

George,

getDate() is a SQL server function and ASP page cannot interpret it...

i agree with rac2, you are seeing a default date of 1900 because of the empty string being passed to the database...make sure you are passing the variable correctly...

-DNG

 

[gmmastros]

DNG,

Thanks for pointing that out. You'll notice that ASP's involvment with the GetDate() function is to simply store it in a string. So, eventually ASP would see..

queryStr = "[tt][blue]INSERT INTO tblPDRitem ( pdrItemCreationTime, packageName, packageType, actionRequested, distributionServers) VALUES(GetDate(), 'SomeValue', 'AnotherValue','SomethingElse','TheLastOne')[/blue][/tt]"

When this string is executed, the sql engine would appropriately use GetDate() in insert the servers date and time in to the column.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[boux123]

Cool, thx guys!