CODEWITHSUNDEEP
sqlsql-server-2005
jiten
jiten

Reputation: 5264

Error couse DataTime-varchar(10)

I have database table in which there is a column OccurDate of datatype DateTime.

Data is like this...

06-03-2013 06:47:49
06-03-2013 16:47:49

I have to find InTime,OutTime,WorkDuration.

I am using query like this

Min(convert(varchar(10),OccurDateTime,108)) AS InTime,
Max(convert(varchar(10),OccurDateTime,108)) AS OutTime,

but I am facing problem while calculating workDuration because the 

workDuration = 16:30:00 - InTime    //16:30:00 means 4:30 pm

and Intime is now varchar(10) so it cannot subtract from Datetime.......

The query that I am using..

SELECT UserID AS EmpCode,FirstName,LastName,Department,convert(varchar(10),OccurDateTime,103) AS Date,Min(convert(varchar(10),OccurDateTime,108)) AS InTime,
Max(convert(DateTime,OccurDateTime,108)) AS OutTime,
convert(varchar(10),(Max(convert(DateTime,OccurDateTime,108))- Min(convert(DateTime,OccurDateTime,108))),108) as Work,

FROM TTransactionLog1
WHERE convert(varchar(10),OccurDateTime,103) = '14/03/2013'
GROUP BY UserID, FirstName, convert(varchar(10),OccurDateTime,103),LastName,Department

So you guys have any idea......

Upvotes: 1

Views: 122

Answers (3)

Kaf
Kaf

Reputation: 33819

You should be looking for Min/Max Dates not for the Min/Max of varchar. So check Dates before converting them to varchar and convert them as the final thing for the required format.

Also you have to use Datediff function to get the difference of Max and Min dates and when you passing string as Dates, please use ISO format ('yyyymmdd') which is NOT culture specific. Otherwise your query may not work in servers with different cultural settings.

I think following query should work:

SELECT UserID AS EmpCode, FirstName, LastName, Department,
       --Convert(varchar(10), OccurDateTime, 103) AS  Date,
       Convert(varchar(10), Min(OccurDateTime), 108) AS InTime,
       Convert(varchar(10), Max(OccurDateTime), 108) AS OutTime,
       Convert(varchar(10), 
           Datediff(day,Min(OccurDateTime), Max(OccurDateTime)),108) as Work
FROM TTransactionLog1
WHERE OccurDateTime  = '20130314' --Note: ISO format 
GROUP BY UserID, FirstName, --OccurDateTime, 
        LastName, Department

Upvotes: 2

revoua
revoua

Reputation: 2059

WITH A AS (SELECT CONVERT(DATETIME, '16:25:00') InTime)
SELECT
CONVERT(varchar(10),CONVERT(DATETIME, '16:30:00') - InTime,108) workDuration
FROM A

Output: 00:05:00
I believe double convertion will lead you to make datetime calculations first and convert to varchar after.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

If you want to calculate an interval between two datetimes, use datediff().

Upvotes: 0

Related Questions