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
Ask ANY Question

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

Troubleshooting
Research
Professional Opinions
Ask a Question
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

troubleshooting Question

Error when date calculation in query is negative

Avatar of running32
running32 asked on
SQL
5 Comments1 Solution219 ViewsLast Modified:
I am running a query in an aspx page that displays the number of hours between the time Convert(VARCHAR(5),timeOut - timein,14)   as timediff  the problem is that when there is a mistake the and a AM is entered instead of a PM the time is negative.  This generates an error when displaying the page.  How can I modify the query so that if it is negative it stills displays or writes out an error.  Thanks
query="select eh.recordid, firstname + ' ' +  lastname as name,timein, timeout,   Convert(VARCHAR(5),timeOut - timein,14)   as timediff, DATENAME(weekday, timein)as dayofweek, CONVERT(VARCHAR(11), timein, 101) AS timein_date,RIGHT(CONVERT(varchar(19),timein, 100), 7) AS timein_time,(CASE WHEN RIGHT(CONVERT(varchar(19),timeout, 100), 7) is null THEN CONVERT(VARCHAR,'NO TIME ENTERED') ELSE RIGHT(CONVERT(varchar(19),timeout, 100), 7) END) as Timeout_time, description from employeehours as eh inner join employeelist as el on eh.employeeid = el.employeeid inner join masterjobcodelist as mjc on eh.jobcode = mjc.jobcode where eh.employeeid = '" & eid & "'and timein  > CONVERT(datetime, '" & strCurrentdate & "', 102) order by timein"
ASKER CERTIFIED SOLUTION
Avatar of koutny
koutny

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answers