SQL Insert Command

[letsgetsilly]

I am creating a web application in VB where the user can enter into a database their different arrival/departure times during the day. It is my first web application with a database.

Attempted INSERT statement as a test:
INSERT INTO TimeEntry VALUES ('1', '1','10/06/2003 2:54:11 PM', '12/18/2002' ,'12/18/2003', '12/18/2004' ,'12/18/2005', '12/18/2006', '12/18/2007' ,'12/18/2008' ,'12/18/2009')
This statement recieves error:
"Syntax error converting datetime from character string."

My table columns are setup as:

TimeEntryID (primary) [int] (should be an auto increment...not sure how to do this)
UserID (foreign key) [int] (linked to another table)
CurrentDate[datetime]
ArrivalTime[datetime]
DepartLunchTime[datetime]
ReturnLunchTime[datetime]
LeaveTime[datetime]
ArrivalTimeSubmitted[datetime]
DepartLunchTimeSubmitted[datetime]
ReturnLunchTimeSubmitted[datetime]
LeaveTimeSubmitted[datetime]

I would just like to fill in some data so that I can play around with it.

I would like my TimeEntryID to be autogenerated, but I don't know how to do this.

Thanks for any help!

 

[Jeraden]

what type of database is this? Oracle? Mysql? etc?

try putting a to_date('12/18/2002','MM/DD/YYYY') for all your date values so it knows how to translate it from character to date. Usually there is a specific format that it will accept without doing the to_date, but it might depend on the database. For Oracle, you'd need to put them as '18-DEC-2002' to get it to accept it automatically.

for your one with a timestamp, you'd need
'MM/DD/YYY HH:MI:SS PM' as your mask

 

[letsgetsilly]

Thanks for the info, I'll mess around with it. I'm running MS SQL Server 8.0

 

[letsgetsilly]

I have it updating now with this command:
INSERT INTO TimeEntry
(UserID, CurrentDate)
VALUES (4444, '1999-12-11 00:00:00.000')

Or

VALUES (5555, '1999-12-11')

The database now recognizes this in the format as seen above: 'YYYY-MM-DD HH:MM:SS.MSS'

In the near future I will want to determine in my VB code whether or not the user has submitted a time for today's date. If they have, then I will use the 'UPDATE' command instead of Insert.

I was planning on using VB's .NOW() method and comparing it to the database value, but I am unsure whether I can compare the database value with the .NOW() method.

Any suggestions? Thanks for the help!

 

[Jeraden]

again, this might depend on the database, but in oracle at least you can use trunc() on a date and it will strip off the timestamp and just leave you with the base date.

so if you wanted to compare a timestamp value to the time right this second you'd do...
trunc(sysdate) = trunc(whatever_date_column_is_called)
and that will just check if it was sometime today

 

[letsgetsilly]

It looks like if I use Now.Today() in VB, it will give today's date with a standard time of 12:00 AM. So if that remains a consistent time I will be able to compare based on only the current date.