Avatar of Overthere
Overthere
 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

ASPSQL

Avatar of undefined
Last Comment
Overthere

8/22/2022 - Mon
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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??
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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 :}
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.