asked on

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

Ryan Chong

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.

Overthere

ASKER

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

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Overthere

ASKER

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??

Overthere

ASKER

Thank you so much. Your function was better then the string I was creating! Very clever!

Ryan Chong

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.

Overthere

ASKER

Thank you for your thoughts - happy coding :}