MS SQL and column defined as time

Overthere
Overthere used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
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.

Author

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
Software Team Lead
Commented:
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

Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

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

Author

Commented:
Thank you so much. Your function was better then the string I was creating! Very clever!
Ryan ChongSoftware Team Lead

Commented:
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.

Author

Commented:
Thank you for your thoughts - happy coding :}

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial