MS SQL and column defined as time

I am using MS SQL 2014 and the database is running in 2008  compability mode. I am revising a classical ASP page.
I have a table that has 2 columns, CheckIn and CheckOut,  which  have the datatype of "time"
I can not get those two columns to  update. I am using a recordset and the rest of the columns update in the table update just fine, but not those two.
I am extracting it from an XML document and they do have values. I have displayed them etc.
Do I have to convert and then plop it into the columns or what?
This is getting kinda ugly tonight...
		        set rsCC = Server.CreateObject("ADODB.Connection")
		        set rsDD = Server.CreateObject("ADODB.Recordset")
		        rsCC.Open vdbase
                       sqltxt = "Select * from Travel where RecId=" & vsep & vrecid & vsep
	               rsDD.Open sqltxt,rsCC,adOpenDynamic,adLockOptimistic,adCmdText
                if not rsDD.BOF and not rsDD.EOF then    
                    rsDD("NbrOfDays") = vnbrnights
                    rsDD("TotalDays") =  vnbrnights
                    rsDD("CheckIn") =  wrkstarttime
                    rsDD("CheckOut") =  wrkendtime
                    rsDD.Update
                end if 
                rsDD.close
		set rsDD = nothing
		set rsCC = nothing

Open in new window

OverthereAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
make sure you define your time fields in MSSQL such as below?

[CheckIn] [time](7) NULL ,
[CheckOut] [time](7) NULL

Open in new window


for the value of variables: wrkstarttime and wrkendtime, make sure they are in "time format", like:

wrkstarttime = "15:33:42"
wrkendtime = "17:12:08"

Open in new window


this was tested working in my local environment with SQL Server 2008 R2.
0
OverthereAuthor Commented:
Thank you for responding. The columns in the table are defined as [CheckIn] [time](7) in the table etc.
The data is not defined as hh:mm:ss  - that's the problem. They look like this: 1240 or 0845 - it is XML text.
no indicator of am/pm etc. I imagine I might need to break them out and create a string... your thoughts??
Seems to me to be a unwieldy approach..sigh
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
The data is not defined as hh:mm:ss  - that's the problem. They look like this: 1240 or 0845 - it is XML text.
no indicator of am/pm etc. I imagine I might need to break them out and create a string... your thoughts??
yea, so we need a function to format HHMM to hh:mm:ss

so you probably can try:

rsDD("CheckIn") =  formatTime(wrkstarttime)
rsDD("CheckOut") =  formatTime(wrkendtime)

Open in new window


then add this:

Function formatTime(str)
            v = trim(str)
            if len(v) = 4 then
                hh = left(v, 2)
                mm = right(v, 2)
                formatTime = hh & ":" & mm & ":00"
            else
                formatTime = ""
            end if
        End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

OverthereAuthor Commented:
Thank you for responding - very clever function! That did the trick1 I noticed that you do not clear your variables - I am wondering about this because should I always initialize the variables? I have a loop in which sections may be repeated once or many times. Should I clear my work variables. I just don't see that in coding anymore. I am always concerned that I will pick-up junk from the previous loop. I  have a ton of variables in this page - not by choice either....Your thoughts??
0
OverthereAuthor Commented:
Thank you so much. Your function was better then the string I was creating! Very clever!
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Should I clear my work variables. I just don't see that in coding anymore. I am always concerned that I will pick-up junk from the previous loop.
it depends on how you structuring your codes. if you're assigning values into variables in a loop, you probably no need to clear its values before you re-assigning them with values. but it could based on your technical/business logic and that could be an isolated issue for decision making.
0
OverthereAuthor Commented:
Thank you for your thoughts - happy coding :}
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.