We help IT Professionals succeed at work.

MS SQL and column defined as time

Overthere
Overthere asked
on
165 Views
Last Modified: 2017-03-28
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

CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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!
CERTIFIED EXPERT

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 :}

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions