Inserting only time (09:00:00) into SQL server

I'm having problems inserting times into my SQL backend through my VB app
I have a access front end that I am wanting to recreate in VB but when i
execute my SQL insert statement instead of 09:00:00 showing in the field,
01/01/1900 09:00:00 or similar shows, i can insert only a date into the date
field 01/01/2005 and it stores how i want. but i cant seperately store the
time the field is set to datetime.
any one have any ideas, also I am getting an error with the SQL statement i
dont think it likes the ' around the time variable but errors if it isnt
there. If i paste the SQL statement into query analyzer with the ' it runs
no problem.
TIA
some sample code is below
strSQL = "INSERT INTO Appointment (Date_Of_Travel, Time_Of_Appointment,
House, Postcode, CMS_Ref, Agent_ID) VALUES ('" & _
DateTrav & "', '" & txtTime & "', '" & txtHouse & "', '" & txtPCode & "', '"
& txtCMS & "', '" & txtAgentID & "');"
Cmd.CommandText = strSQL
Cmd.Executehi steven,
u cannot store the time alone in the datetime obj. it has to be with the
date part. since u have not specified the date , sql server has picked up th
e
default date : 1/1/1900.
optionally u can think of adding the time along with some date and then
ignore the date part , when ever u operate on this field.
anu
"steven scaife" wrote:

> I'm having problems inserting times into my SQL backend through my VB app
> I have a access front end that I am wanting to recreate in VB but when i
> execute my SQL insert statement instead of 09:00:00 showing in the field,
> 01/01/1900 09:00:00 or similar shows, i can insert only a date into the da
te
> field 01/01/2005 and it stores how i want. but i cant seperately store th
e
> time the field is set to datetime.
> any one have any ideas, also I am getting an error with the SQL statement
i
> dont think it likes the ' around the time variable but errors if it isnt
> there. If i paste the SQL statement into query analyzer with the ' it run
s
> no problem.
> TIA
> some sample code is below
> strSQL = "INSERT INTO Appointment (Date_Of_Travel, Time_Of_Appointment,
> House, Postcode, CMS_Ref, Agent_ID) VALUES ('" & _
> DateTrav & "', '" & txtTime & "', '" & txtHouse & "', '" & txtPCode & "',
'"
> & txtCMS & "', '" & txtAgentID & "');"
> Cmd.CommandText = strSQL
> Cmd.Execute
>|||SQL Server doesn't have a TIME datatype. DATETIME always stores both
date and time. Looking at your INSERT statement it seems you have
separate columns for date and time. That doesn't seem like a good idea.
Why use one column instead of two? You can easily separate the date and
time elements when you query the table, or just format it differently
to show only date or only time on screen.
Don't build and execute dynamic SQL strings from user input. It exposes
you to SQL Injection vulnerabilities (Google for it if you don't know).
Data-access code in your client app also increases database maintenance
problems because it forces you to change your application when you make
schema changes. All data access should usually be performed through
parameterized stored procedures.
David Portas
SQL Server MVP
--|||Hi
I'd create stored procedure that accept few parameters
declare @.dt DATETIME,@.t CHAR(5)
SET @.dt=GETDATE()
SET @.t='09:00'
CREATE TABLE #Test
(
col DATETIME
)
INSERT INTO #Test SELECT GETDATE()
INSERT INTO #Test SELECT CAST(CONVERT(CHAR(10),@.dt,112)+@.t AS DATETIME)
SELECT * FROM #Test
"steven scaife" <stevenscaife@.discussions.microsoft.com> wrote in message
news:A60949B8-0E18-46D0-9937-A01761A3FB8D@.microsoft.com...
> I'm having problems inserting times into my SQL backend through my VB app
> I have a access front end that I am wanting to recreate in VB but when i
> execute my SQL insert statement instead of 09:00:00 showing in the field,
> 01/01/1900 09:00:00 or similar shows, i can insert only a date into the
date
> field 01/01/2005 and it stores how i want. but i cant seperately store
the
> time the field is set to datetime.
> any one have any ideas, also I am getting an error with the SQL statement
i
> dont think it likes the ' around the time variable but errors if it isnt
> there. If i paste the SQL statement into query analyzer with the ' it
runs
> no problem.
> TIA
> some sample code is below
> strSQL = "INSERT INTO Appointment (Date_Of_Travel, Time_Of_Appointment,
> House, Postcode, CMS_Ref, Agent_ID) VALUES ('" & _
> DateTrav & "', '" & txtTime & "', '" & txtHouse & "', '" & txtPCode & "',
'"
> & txtCMS & "', '" & txtAgentID & "');"
> Cmd.CommandText = strSQL
> Cmd.Execute
>