Convert Datetime to Integer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert Datetime to Integer

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2009-11-09 : 11:53:11

I have a table that I want to update with the sum values of other columns within that table. Its a time card table so it records a datetime for arrival, lunch out, lunch in, and departure. That said, I have a "Total" column that I want to update with the mathematical formula of Total = ([Departure]-[Arrival])-([LunchIn]-[LunchOut]).

I am going to schedule this to run on a job at the end of each day. The database is very small, so it won't be a taxing T-SQL to run each night. I have contemplated not storing the data, but displaying through query. I haven't decided enough about the future of this database yet in order to know which is a better route for me to go, so I'm trying to cover my bases now.

I currently have the following SQL, but I am getting the message that an implicit conversion from datatype datetime to int is not allowed and I must used the CONVERT function. I'm not well versed on the sql syntax to use CONVERT, so hence my post. Can anyone help?

UPDATE dbo.TimeCard Set Total=T.Total
FROM
(
SELECT TimeCard.Date, TimeCard.SSN, Round(((([Depart]-[Arrive])-([LunchIn]-[LunchOut]))*24),2) AS Total
FROM TimeCard
)T
WHERE
dbo.TimeCard.SSN=T.SSN and dbo.TimeCard.Date=T.Date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 11:56:22

replace Round(((([Depart]-[Arrive])-([LunchIn]-[LunchOut]) with

(datediff(dd,[Depart],[Arrive])-datediff(dd,[LunchIn],[LunchOut]))*24

benking9987
Posting Yak Master

124 Posts

Posted - 2009-11-09 : 12:42:31

Thanks for the reply. I made the change you suggested. I am not getting any errors when I run, but I'm not getting the desired result either. here is what I have:


UPDATE dbo.TimeCard Set Total=T.Total
FROM
(
SELECT TimeCard.Date, TimeCard.SSN, ((datediff(dd,[Depart],[Arrive])-datediff(dd,[LunchIn],[LunchOut]))*24) AS Total
FROM TimeCard
)T
WHERE
dbo.TimeCard.SSN=T.SSN and dbo.TimeCard.Date=T.Date

All records in the Total column are being populated with "0"

Here is an example of the resulting data:

quote:

SSN Date Arrive LunchOut LunchIn Depart Total

123456789 2009-11-09 00:00:00.000 2009-11-09 07:50:00.000 2009-11-09 12:15:00.000 2009-11-09 13:15:00.000 2009-11-09 17:15:00.000 0.00
234567890 2009-11-09 00:00:00.000 2009-11-09 08:00:00.000 2009-11-09 15:36:00.000 2009-11-09 16:36:00.000 2009-11-09 20:52:00.000 0.00
345678901 2009-11-09 00:00:00.000 2009-11-09 08:00:00.000 2009-11-09 12:00:00.000 2009-11-09 13:00:00.000 2009-11-09 16:15:00.000 0.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 12:48:52

what have passed as values for dates?

benking9987
Posting Yak Master

124 Posts

Posted - 2009-11-09 : 13:16:27

Sorry, I don't understand your question.

benking9987
Posting Yak Master

124 Posts

Posted - 2009-11-09 : 13:58:22

I think I figured it out. We had "dd". I wanted to return values for hours/seconds. Here is what I changed the parts to:

Select SSN, Date, datediff(ss,[Arrive],[Depart])/3600 from TimeCard

SELECT SSN, Date, datediff(ss,[LunchOut],[LunchIn])/3600 from TimeCard

I am getting whole numbers now. Any way to get decimals to 2 decimal places? Ex: 9.05, 12.52.....

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-11-09 : 14:50:50

[code]select
ElapsedHours =
convert(numeric(10,2),round(datediff(ss,0,Depart-Arrive)/3600.00,2))
from
( --Test Data
Select
Arrive = convert(datetime,'20091104 23:44:59.937'),
Depart = convert(datetime,'20091109 03:13:24.330')
) a[/code]
Results:
[code]ElapsedHours
------------
99.47[/code]

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-09 : 14:51:09

Divide by "/ 3600.0"

N 56°04'39.26"
E 12°55'05.63"

benking9987
Posting Yak Master

124 Posts

Posted - 2009-11-09 : 18:43:12

Michael Jones, thank you for your reply. This seems to be returning the correct mathematical values that I need. Now, incorportating the update statement and the LunchIn LunchOut, I have the following SQL:


UPDATE dbo.TimeCard Set Total=T.Total
FROM
(
SELECT convert(numeric(10,2),round((datediff(ss,0,[Depart]-[Arrive])/3600.00)-(datediff(ss,0,[LunchIn]-[LunchOut])/3600.00),2)) as Total
FROM TimeCard
)T

I still have something wrong as I'm getting: "Msg 815, Level 16, State 8, Line 1. rithmetic overflow error converting numeric to data type numeric.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-10 : 12:46:27

whats the datatype defined for Total field? make sure it has sufficient precision and scale to hold largest of result

benking9987
Posting Yak Master

124 Posts

Posted - 2009-11-10 : 17:47:25

That was it Visakh16! I had numeric(2,2), but it wasn't sufficient. I put it at numeric(18,10) and it now handles just fine. Here is the final SQL I'm using:


UPDATE dbo.TimeCard Set Total=T.Total
FROM
(
SELECT SSN, [Date], convert(numeric(10,2),round((datediff(ss,0,[Depart]-[Arrive])/3600.00)-(datediff(ss,0,[LunchIn]-[LunchOut])/3600.00),2)) as Total
FROM TimeCard
)T
WHERE TimeCard.SSN = T.SSN and TimeCard.Date = T.Date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources