Inserting time without date into SQL server

[wwwcoder]

Hi

Does anyone know how I can enter a time into a datetime field with no date. The query I am using is as follows

Insert into TRIPS (ship,dep_date,sail_time,return_time,sail_from,destination,adult_price,senior_price) VALUES ('TEST','6/21/2001','10:05 AM','1:05 PM','Inverness','Glasgow',10,5)

This, however, this inserts the date as
1/1/1900 10:05:00 AM

Help gratefully appreciated
Dave

 

[link9]

try this:

dim sql
sql = "Insert into TRIPS (ship,dep_date,sail_time,return_time,sail_from,destination,adult_price,senior_price) VALUES ('TEST','6/21/2001','"
sql = sql & formatDateTime("10:05 AM", 3)
sql = sql & "','1:05 PM','Inverness','Glasgow',10,5)"

Not positive that will work, but the formatDateTime() function is setting (by stating the constant, 3) to the subtype of vbLongTime, which should fix the problem.

For more info on the formatDateTime() and other possible solutions, check out

http://www.devguru.com

and their vbScript quick reference guide.

good luck!
Paul Prewett

 

[wwwcoder]

Thanks

but still noy joy

Dave Ross

 

[link9]

If you are using Access, you can set the input mask of the field to be what you want...

If you using SQL Server, then look into the CAST() function for T-SQL in SQL Server Books Online to get a jump start on how to get it to format as you want it to. You would run this CAST() on the INSERT statement.

hope that helped
Paul Prewett