#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    3
    Rep Power
    0

    vbscript date truncated


    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 = "0" & DBMonth
    end if

    DBYear = "2003"


    accessDB = "histcrec" & DBMonth & DBYear & ".mdb"

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

    'msgbox accessDB

    'Build SQLstring

    sqlstring = "select * from agentlogondata"

    set Conn = createobject("adodb.connection")
    set Rs = createobject("adodb.recordset")
    Conn.open = strconn
    Rs.open SqlString, Conn
    LpCount = 0

    err.clear

    if Rs.eof then

    set ConnWrite = createobject("adodb.connection")

    ConnWrite.open = "DSN=CallScan"

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

    ConnWrite.Execute(SQLinsert)

    else:

    set ConnWrite = createobject("adodb.connection")
    ConnWrite.open = "DSN=CallScan"


    do until Rs.eof


    SqlInsert = ""
    Sqlinsert = SqlInsert & "(agent_id, group_id, Console_Ext_id, V_logon_date, V_logoff_date, "

    Sqlinsert = SqlInsert & "logon_date, logon_time, logoff_date, logoff_time)"

    Sqlinsert = SqlInsert & " values ('" & rs("agent_id") & "', '" & rs("group_id") & "', '" & rs("turret_id") & "', '" & rs("logon_date") & "', '" & rs("logoff_date") & "', "



    'build logon date

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


    sqlinsert = sqlinsert & "'" & var_logon_date & "', "


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


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


    sqlinsert = sqlinsert & "'" & var_logoff_date & "', "


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

    'inputbox "Test", "Test", "Insert into agentlogondata " & SQLinsert

    ConnWrite.Execute("Insert into agentlogondata " & SQLinsert)

    if err.number <> 0 then

    ConnWrite.Execute("Insert into agentlogondataerr " & 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
    ****************************************************
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    a northern town
    Posts
    74
    Rep Power
    13
    You can add a CONVERT() function to the sql for each of the dates you wish to insert into the sql server db. This will convert from char/text data to a datetime value. This is preferrable to breaking down and re-assembling pieces of data the way you seem to be doing.

    HTH.
    /* measure twice, cut once */

IMN logo majestic logo threadwatch logo seochat tools logo