Date/Time not displaying seconds

[johnpayback]

I am running a query against a database that will generate a CSV report with the data. My problem is that the date/time fields that I'm querying are not adding the seconds into my report. The database does have the seconds in it so I'm baffled. Below is the script. What exactly am I doing wrong? It is pulling the date, hour and minutes just fine just no seconds.

Dim db
Dim strserver

db = "database"

strServer = InputBox("Please enter server name: ")
Const fsoForWriting = 2
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Create Connection
set con = CreateObject("ADODB.Connection")
con.CommandTimeout = 9500
strCon = "driver={SQL SERVER};server=" & strserver & ";uid=sa;pwd=password;database=" & db & ""
con.Open strCon

'Create Command
sql = "SELECT user_id.userid as Sym_ID, user_id.LName as Last_Name, user_id.FName as First_Name, user_id.MName as Middle_Name, master_index.RemoteServer as APP_Server, master_index.filedatetime as FileDateTime, master_agent.StartTime as Start_Time, master_agent.stoptime as Stop_Time, master_agent.tf_played as Has_been_Played, master_index.filelength as Record_Length FROM user_id, master_agent, master_index WHERE user_id.userid = master_agent.userid AND master_agent.recnumber = master_index.recordnumber AND master_agent.starttime between '2007-01-01 00:00:00' and '2007-01-31 23:59:59' AND master_agent.tf_played = '1' ORDER BY master_agent.starttime"

'Open the text file
Dim objTextStream, objTextFile
objTextFile = "Query_"&(strserver)&"_"&(DatePart("m",Date)&DatePart("d",Date)&DatePart("yyyy",Date))&"_"&Hour(Now)&Minute(Now)&Second(Now)&".csv"
Set objText = objFSO.CreateTextFile(objTextFile, True)
objText.Close
Set objTextStream = objFSO.OpenTextFile (objTextFile, fsoForWriting, False)
       
set rs = CreateObject("ADODB.Recordset")
rs.Open sql, con

rs.MoveFirst
If Not rs.EOF Then
  s = ""
  For i = 0 To rs.Fields.Count - 1
    s = s & "," & rs.Fields(i).Name
  Next
  objTextStream.Writeline Mid(s, 2)
End If
While Not rs.EOF
  s = ""
  For i = 0 To rs.Fields.Count - 1
    s = s & "," & rs.Fields(i)
  Next
  objTextStream.Writeline Mid(s, 2)
  rs.MoveNext
Wend
rs.Close
con.Close
objTextStream.Close
Set con = Nothing 
Set rs = Nothing
Set sql = Nothing 
wscript.quit

JP

 

[mrmovie]

what happens when you echo rs.Item(Start_Time), i take it doesnt contain your seconds?

 

[johnpayback]

You are correct. What does that tell me when it doesn't display them?

JP

 

[barny2006]

how is starttime defined in the table?

 

[djtech2k]

The database squema would be nice to have to diagnose this problem. It sounds like a field to code issue on the surface. What I mean is it sounds like your database is storing it one way and your code is requesting it in a different way.

 

[barny2006]

i usually get around this problem by formatting this field as TS (time stamp) which is not available in access. i format it like this and write it to the text field.
yyymmdd:hh:nn:ss:iiii
year/month/day
hours (military)
minutes
seconds
miliseconds
20070226:13:04:20:0645

 

[johnpayback]

It actually stores it in the database as a datetime. The database is MSSQL 2000.

2004-03-22 12:50:07.000

For some reason it is only putting this part in the report that I am generating with the script above.

2004-03-22 12:50

Hope this helps a little more to help you understand my problem.

JP

 

[Gazzaspc]

I had this very problem in my Microsoft Access database (connecting to SQL 2000 data) and found that the data field in my table was created as a smalldatetime field.
When I changed it to a datetime field it saved the seconds properly.