Inserting date fields

The Mighty Dubs

Hi
This is an easy one i reckon i just have been staring at it so long i just know the answer is on the end of my nose.

Basically i have a table which has 2 date fields in it. DateCreated and DateModified
The fields need to be fill in as follows:

1. DateCreated when the initial data is entered into tbl row
2. DateModified when the data is updated.

I have an insert statement for the DateCreated using a hidden field but this is not trapping the date for me, its just leaving the field blank.

<input name="DateEntered" type="hidden" id="DateEntered" value="<%= NOW() %>" >

I reckon if i can get this bit right for the first insert statement, i can get it working for the modify statement too

oh and IM using VB ASP with a SQLServer 2000 DB

Cheers

John_Mc

The Mighty Dubs wrote: »

Hi
This is an easy one i reckon i just have been staring at it so long i just know the answer is on the end of my nose.

Basically i have a table which has 2 date fields in it. DateCreated and DateModified
The fields need to be fill in as follows:

1. DateCreated when the initial data is entered into tbl row
2. DateModified when the data is updated.

I have an insert statement for the DateCreated using a hidden field but this is not trapping the date for me, its just leaving the field blank.

<input name="DateEntered" type="hidden" id="DateEntered" value="<%= NOW() %>" >

I reckon if i can get this bit right for the first insert statement, i can get it working for the modify statement too

oh and IM using VB ASP with a SQLServer 2000 DB

Cheers

What date format is Now() outputting? Does this conform to the Date Formats accepted by SQL Server?

I had this problem before but with MySQL. I formatted the date into Universal format and it worked fine.

kayos

You could just use GETDATE() in your SQL....

Eoin

Response.Write out the SQL statement and run it directly against the database using the query editor, and that will tell you if the date is in the correct format.

Don't use hidden form fields until you are certain the values are correct.

The Mighty Dubs

kayos wrote: »

You could just use GETDATE() in your SQL....

I cant really use the GETDATE() sql because each time the row of info is modified that date will change accordingly, thus loosing the creating date of that row of info...

Eoin

The Mighty Dubs wrote: »

I cant really use the GETDATE() sql because each time the row of info is modified that date will change accordingly, thus loosing the creating date of that row of info...

No - when you are inserting the row you use getDate for both created and modified. When you are updating the row, you simply leave the createdDate alone altogether.

kayos

eoin_s wrote: »

No - when you are inserting the row you use getDate for both created and modified. When you are updating the row, you simply leave the createdDate alone altogether.

What he said.....

The Mighty Dubs

ah i getcha. Nice one,

The Mighty Dubs

Ok that kind of works, Im getting an error(see below). I checked the insert statement and its to do with the regional date settings. I need to convert it by the looks of it. Is there a simple conversion script i can use to get it to convert to mm:hh dd/mm/yyyy ?


Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
/spark2008/adminarea/newartapp.asp, line 146

Eoin

Would putting the time before the date not make ordering it kind of awkward?

kayos

The Mighty Dubs wrote: »

Ok that kind of works, Im getting an error(see below). I checked the insert statement and its to do with the regional date settings. I need to convert it by the looks of it. Is there a simple conversion script i can use to get it to convert to mm:hh dd/mm/yyyy ?


Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
/spark2008/adminarea/newartapp.asp, line 146

Hmmm hard to see how you could get that error if your using GETDATE() on the SQL Server.

But any way in SQL Server if you want to convert a date to a given format the CONVERT function allows you to do this. http://msdn.microsoft.com/en-us/library/ms187928.aspx