TIME formatting problem

[DougP]

I am using Access 2000 to write info to a SQL server database
When I add a time like so
It shows up in the table as this
1/1/1900 7:00:00 AM
the Field type is "datetime"
here is the VBA ADO code:
==================================
Dim ThisTime As Date
Dim Conn As ADODB.Connection
Dim Rs1 As ADODB.Recordset

Dim SQLCode As String

Set Conn = New ADODB.Connection
Set Rs1 = New ADODB.Recordset

Conn.Open "driver=SQL Server;server=smallbserver;uid=sa;pwd=;database=Universal;"
ThisTime = "6:00am"
For a = 1 To 12
ThisTime = ThisTime + "1:00"
'Debug.Print ThisTime
SQLCode = "Insert Into [DailyEvents] (TimeON, DateON) Values ('" & ThisTime & "', '" & Format(DateNeeded, "mm/dd/yy&quot & "')"
Rs1.Open SQLCode, Conn, adOpenStatic, adLockOptimistic
'Rs1.Close
Next

Set Rs1 = Nothing
Set Conn = Nothing


=========
Any Help would be appreciated DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[foxdev]

It shows up in the table as this
1/1/1900 7:00:00 AM

Doug, you didn't specify the results you wanted, so I'm not sure what your question is. The date portion, 1/1/1900, is because you are not specifying a date in your code/Insert statement, so it is defaulted to 01/01/1900. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[DougP]

I would like just the time in the "TimeON" on field without the 1/1/1900 in front of it.
the "DateOn" just shows 6/14/01 with no time on the end of it.
When this was an Access database it just showed the time and no 1/1/1900 in front of it.
So I want just the date in the DateON and just the time in the TimeON field.
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[tlbroadbent]

The datetime storage format in SQL server is fixed. It includes a date part and a time part - non-separatable. You can't change that. You can change the display format either in T-SQL or in your client application.

Select convert(char(8), DateCol, 14) As TimeOnly, ...
From tbl

Datetime style 14 used with the convert function returns the time as hh:mm:ss.mmmm. In my example, I truncated the result at 8 characters or hh:mm:ss. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[DougP]

Ok
here is a weird one for you.
If I open the Enterprise Manager and open that Table to view the records I can delete the date 1/1/1900 off the front and it stays off. I can edit off the 1/1/1900 in Access too.

It's just writing it with "Insert Into" that it puts the 1/1/1900
Maybe I need to make a function that strips that off after it saves the data with Insert Into. There are only 10 or so records added each day.

PS here is a screen capture of my form

http://www.barcodeone.com/what_i_want_to_see.htm

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[redlam]

Hiya Doug.
You won't be able to trim off the date. SQL stores date and time together no matter what you do. The only thing you can do is to change your datatype to varchar (not ideal) or simply use Terry's suggestion above to display the data in your desired format. If this were my table, I would combine the DateOn and TimeOn fields into one field since you'll have to convert each one in the form anyway. Not to mention you'd be saving space.
EX:
Stored in database -
DateTimeOn = 06/14/01 15:48:00

Displayed on form -
convert(varchar, DateTimeOn, 1) as DateOn
convert(varchar, DateTimeOn, 14) as TimeOn

 

[redlam]

Heh. Got trigger happy and posted before I was done... should've looked like this:

Displayed on form -
convert(varchar, DateTimeOn, 1) as DateOn = 06/14/00
convert(varchar, DateTimeOn, 14) as TimeOn = 15:48:00