TRUNCATED TIME FIELDS

[pamo]

I am copying data from a access table into a sql table using a vbscript as shown below in BETWEEN THE *****. When executing the script the script performs some data format changes. as the date fields are split into a logon date + time and logoff date + time.

The problem i have is that the time fields in the sql table are truncated so for eg: 07:06:15 becomes 7:6:15 All leading zeros are removed!!! Does any one have any idea how i can keep the time format in a hh:mm:ss format without removing these zeros

the format that the time field is being copied into the table agentlogondata is a char of size 15.

please help as this problem is really gettin me down!! cheers

CODE
****************************************************
dim varLOOP
dim Sqlstring
dim SqlInsert
dim Conn
dim Rs
dim ConnWrite
dim objcmd
dim strconn
dim LpCount
dim var_day
dim var_month
dim var_year
dim var_logon_date
dim var_logoff_date


on error resume next


varLOOP = 1


Do until varLOOP =13


'msgbox varLOOP

'build connection string to required DB
'work out month from loop counter

DBMonth = varLOOP


if DBMonth < 10 then
DBMonth = &quot;0&quot; & DBMonth
end if

DBYear = &quot;2003&quot;


accessDB = &quot;histcrec&quot; & DBMonth & DBYear & &quot;.mdb&quot;

strconn=&quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=&quot;
strconn=strconn & &quot;Q:\rtarchdata\&quot; & accessDB & &quot;;&quot;

'msgbox accessDB

'Build SQLstring

sqlstring = &quot;select * from agentlogondata&quot;

set Conn = createobject(&quot;adodb.connection&quot
set Rs = createobject(&quot;adodb.recordset&quot
Conn.open = strconn
Rs.open SqlString, Conn
LpCount = 0

err.clear

if Rs.eof then

set ConnWrite = createobject(&quot;adodb.connection&quot

ConnWrite.open = &quot;DSN=CallScan&quot;

Sqlinsert = SqlInsert & &quot;insert into agentlogondataerr (agent_id)&quot;
Sqlinsert = SqlInsert & &quot;values ('0')&quot;

ConnWrite.Execute(SQLinsert)

else:

set ConnWrite = createobject(&quot;adodb.connection&quot
ConnWrite.open = &quot;DSN=CallScan&quot;


do until Rs.eof


SqlInsert = &quot;&quot;
Sqlinsert = SqlInsert & &quot;(agent_id, group_id, Console_Ext_id, V_logon_date, V_logoff_date, &quot;

Sqlinsert = SqlInsert & &quot;logon_date, logon_time, logoff_date, logoff_time)&quot;

Sqlinsert = SqlInsert & &quot; values ('&quot; & rs(&quot;agent_id&quot & &quot;', '&quot; & rs(&quot;group_id&quot & &quot;', '&quot; & rs(&quot;turret_id&quot & &quot;', '&quot; & rs(&quot;logon_date&quot & &quot;', '&quot; & rs(&quot;logoff_date&quot & &quot;', &quot;



'build logon date

var_day = datepart (&quot;d&quot;, rs(&quot;logon_date&quot)
var_month = datepart (&quot;m&quot;, rs(&quot;logon_date&quot)
var_year = datepart (&quot;yyyy&quot;, rs(&quot;logon_date&quot)
Var_month = monthname(var_month)
var_logon_date = var_day & &quot; &quot; & var_month & &quot; &quot; & var_year


sqlinsert = sqlinsert & &quot;'&quot; & var_logon_date & &quot;', &quot;


sqlinsert = sqlinsert & &quot;'&quot; & datepart(&quot;h&quot;, rs(&quot;logon_date&quot) & &quot;:&quot; & datepart(&quot;n&quot;, rs(&quot;logon_date&quot) & &quot;:&quot; & datepart(&quot;s&quot;, rs(&quot;logon_date&quot) & &quot;', &quot;


'build logoff date
var_day = datepart (&quot;d&quot;, rs(&quot;logoff_date&quot)
var_month = datepart (&quot;m&quot;, rs(&quot;logoff_date&quot)
var_year = datepart (&quot;yyyy&quot;, rs(&quot;logoff_date&quot)
var_month = monthname(var_month)
var_logoff_Date = var_day & &quot; &quot; & var_month & &quot; &quot; & var_year


sqlinsert = sqlinsert & &quot;'&quot; & var_logoff_date & &quot;', &quot;


sqlinsert = sqlinsert & &quot;'&quot; & datepart(&quot;h&quot;, rs(&quot;logoff_date&quot) & &quot;:&quot; & datepart (&quot;n&quot;, rs(&quot;logoff_date&quot) & &quot;:&quot; & datepart (&quot;s&quot;, rs(&quot;logoff_date&quot) & &quot;')&quot;

'inputbox &quot;Test&quot;, &quot;Test&quot;, &quot;Insert into agentlogondata &quot; & SQLinsert

ConnWrite.Execute(&quot;Insert into agentlogondata &quot; & SQLinsert)

if err.number <> 0 then

ConnWrite.Execute(&quot;Insert into agentlogondataerr &quot; & SQLinsert)

err.clear

end if
LPcount = LPcount + 1
Rs.movenext

loop
ConnWrite.close
set ConnWrite = nothing
end if
Rs.close
Conn.close

'increment by one
varLOOP = varLOOP + 1

Loop


set Rs = nothing
set Conn = nothing
****************************************************

 

[GBudbill]

The datepart function is truncating your dates before you insert them into the database. You need to fix the format before posting them. Try the following which demonstrates the problem and a solution.

<example>
dtNow = Now
dtDay = datepart(&quot;d&quot;, dtNow)
dtMonth = datepart(&quot;m&quot;, dtNow)
dtYear = datepart(&quot;yyyy&quot;, dtNow)

wscript.echo dtMonth, dtDay, dtYear

dtDay = Right(&quot;00&quot; & dtDay, 2)
dtMonth = Right(&quot;00&quot; & dtMonth, 2)

wscript.echo dtMonth, dtDay, dtYear
</example>