Author |
Topic |
jbosco1988
Starting Member
46 Posts |
Posted - 2007-08-22 : 12:38:21
|
I am trying to create a view with and Employee Start time and End time for each day. How convert 53518755.0 to a Date Time?Thanks,James Bosco |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-08-22 : 12:54:50
|
select DATEADD(minute,53518755.0 ,0). I am assuming 53518755.0 is the number of minutes since '01/01/1900'Jim |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-22 : 12:55:54
|
It depends.What date (and time) is 53518755.0 supposed to respresent? E 12°55'05.25"N 56°04'39.16" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-22 : 12:56:59
|
E 12°55'05.25"N 56°04'39.16" |
|
|
jbosco1988
Starting Member
46 Posts |
Posted - 2007-08-22 : 13:01:44
|
SELECT CONVERT(smalldatetime, START_MOMENT) AS EXPR1FROM dbo.DET_SEGExample1/4/2002 12:00:00 AM |
|
|
jbosco1988
Starting Member
46 Posts |
Posted - 2007-08-22 : 13:04:53
|
I keep gettin the Arithmetic overflow errorSQLSRv32.dll |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-08-22 : 13:26:35
|
Please do tell us what 53518755.0 is. Is it minutes from '01/01/1900'? We're dying to know. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-22 : 13:29:37
|
Maybe you just could please stress down, and answer our questions first? E 12°55'05.25"N 56°04'39.16" |
|
|
jbosco1988
Starting Member
46 Posts |
Posted - 2007-08-23 : 11:58:44
|
Please see above. Also nobody was stressing. No need to tell me to stress down. Thanks. It is supposed to represent a Clock in Clock out Date and time.Example1/4/2002 12:00:00 AM |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-08-23 : 12:05:21
|
Could you please post several examples of the number you are trying to convert and the exact datetime it is supposed to represent?CODO ERGO SUM |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-08-23 : 12:06:09
|
53518755.0 this represents a clock in and clock out date? Is the time really 5:35:18.755? I am the one stressing, I so desperately want to know the answer to the question asked so often: what exactly is 53518755.0?Jim |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-23 : 12:20:07
|
[code]declare @i decimal(9,1)set @i = 53518755.0SELECT REPLACE(STR(@i, 9, 0), ' ', '0'), STUFF(STUFF(STUFF(REPLACE(STR(@i, 9, 0), ' ', '0'), 7, 0, '.'), 5, 0, ':'), 3, 0, ':')[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
jbosco1988
Starting Member
46 Posts |
Posted - 2007-08-23 : 12:23:07
|
First Jim I have answered you question. Michael Valentine JonesHere are some examples. See Below as to what I need them to be. Agent ID Start Stop 280001 55638330.0 55638390.0 280001 55638225.0 55638240.0 280001 55638495.0 55638510.0 280001 55638120.0 55638660.0 280001 55638120.0 55638660.0 280001 55639770.0 55639830.0 280001 55639665.0 55639680.0 280001 55639935.0 55639950.0 280001 55639560.0 55640100.0 280001 55639560.0 55640100.0 280001 55641210.0 55641270.0 280001 55641105.0 55641120.0 280001 55641375.0 55641390.0 280001 55641000.0 55641540.0 280001 55641000.0 55641540.0 280001 55681590.0 55681650.0 280001 55681485.0 55681500.0 280001 55681755.0 55681770.0 280001 55681380.0 55681920.0Here is what I am trying to get in SQL. I can get these results in Access with CDATE. However Access is to slow.AGENTID Stop Start280001 10/3/2005 4:00:00 PM 10/3/2005 3:45:00 PM280001 10/3/2005 6:30:00 PM 10/3/2005 5:30:00 PM280001 10/3/2005 8:30:00 PM 10/3/2005 8:15:00 PM280001 10/3/2005 11:00:00 PM 10/3/2005 2:00:00 PM280001 10/3/2005 11:00:00 PM 10/3/2005 2:00:00 PM280001 10/4/2005 4:00:00 PM 10/4/2005 3:45:00 PM280001 10/4/2005 6:30:00 PM 10/4/2005 5:30:00 PM280001 10/4/2005 8:30:00 PM 10/4/2005 8:15:00 PM280001 10/4/2005 11:00:00 PM 10/4/2005 2:00:00 PM280001 10/4/2005 11:00:00 PM 10/4/2005 2:00:00 PM280001 10/5/2005 4:00:00 PM 10/5/2005 3:45:00 PM280001 10/5/2005 6:30:00 PM 10/5/2005 5:30:00 PM280001 10/5/2005 8:30:00 PM 10/5/2005 8:15:00 PM280001 10/5/2005 11:00:00 PM 10/5/2005 2:00:00 PM280001 10/5/2005 11:00:00 PM 10/5/2005 2:00:00 PM280001 10/6/2005 4:00:00 PM 10/6/2005 3:45:00 PM280001 10/6/2005 6:30:00 PM 10/6/2005 5:30:00 PM280001 10/6/2005 8:30:00 PM 10/6/2005 8:15:00 PM280001 10/6/2005 11:00:00 PM 10/6/2005 2:00:00 PM280001 10/6/2005 11:00:00 PM 10/6/2005 2:00:00 PM |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-23 : 12:32:11
|
Are the numbers sorted?First row280001 55638330.0 55638390.0Represents280001 10/3/2005 3:45:00 PM 10/3/2005 4:00:00 PMI am just wondering because you posted 19 numeric value and 20 datetime values. E 12°55'05.25"N 56°04'39.16" |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-08-23 : 12:35:54
|
What I asked for is examples of numbers and the exact datetime they supposed to represent.Something like this:55638330.0 = 2007-05-23 23:23:45.997CODO ERGO SUM |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-23 : 12:38:39
|
Please post the CDATE solution you have in ACCESS today. E 12°55'05.25"N 56°04'39.16" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-23 : 12:44:09
|
I have found it!Base date is neither 19000101 nor 18991230 (as access normally has).The base date above is "1899-12-20 21:00:00" "1899-12-21". E 12°55'05.25"N 56°04'39.16"EDIT: Corrected when discovered flawed sample data |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-23 : 12:51:09
|
[code]-- Prepare sample datadeclare @agent table (AgentID int, Start money, Stop money)insert @agentselect 280001, 55638330.0, 55638390.0 union all select 280001, 55638225.0, 55638240.0 union allselect 280001, 55638495.0, 55638510.0 union allselect 280001, 55638120.0, 55638660.0 union allselect 280001, 55638120.0, 55638660.0 union allselect 280001, 55639770.0, 55639830.0 union allselect 280001, 55639665.0, 55639680.0 union allselect 280001, 55639935.0, 55639950.0 union allselect 280001, 55639560.0, 55640100.0 union allselect 280001, 55639560.0, 55640100.0 union allselect 280001, 55641210.0, 55641270.0 union allselect 280001, 55641105.0, 55641120.0 union allselect 280001, 55641375.0, 55641390.0 union allselect 280001, 55641000.0, 55641540.0 union allselect 280001, 55641000.0, 55641540.0 union allselect 280001, 55681590.0, 55681650.0 union allselect 280001, 55681485.0, 55681500.0 union allselect 280001, 55681755.0, 55681770.0 union allselect 280001, 55681380.0, 55681920.0-- Show the expected outputSELECT AgentID, Start, Stop, dateadd(minute, start, '18991220 21:00:00'), dateadd(minute, stop, '18991220 21:00:00')from @agentorder by agentid, start[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
jbosco1988
Starting Member
46 Posts |
Posted - 2007-08-23 : 13:06:41
|
Michael Valentine Jones55638330.0 55638390.0 10/3/2005 4:00:00 PM 10/3/2005 3:45:00 PM55638225.0 55638240.0 10/3/2005 6:30:00 PM 10/3/2005 5:30:00 PM55638495.0 55638510.0 10/3/2005 8:30:00 PM 10/3/2005 8:15:00 PM55638120.0 55638660.0 10/3/2005 11:00:00 PM 10/3/2005 2:00:00 PM55638120.0 55638660.0 10/3/2005 11:00:00 PM 10/3/2005 2:00:00 PM55639770.0 55639830.0 10/4/2005 4:00:00 PM 10/4/2005 3:45:00 PM55639665.0 55639680.0 10/4/2005 6:30:00 PM 10/4/2005 5:30:00 PM55639935.0 55639950.0 10/4/2005 8:30:00 PM 10/4/2005 8:15:00 PM PesoAccessStop: CDate([STOP_MOMENT]/1440) |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-08-23 : 13:07:10
|
And Peso gets the cheese!Jim |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-23 : 13:25:48
|
quote: Originally posted by jimf And Peso gets the cheese!
Thank you!jbosco1988, your sample data above need improvement!Why are you so sloppy? Don't you want help?declare @twit table (num1 int, num2 int, dt1 smalldatetime, dt2 smalldatetime)insert @twitselect 55638330.0, 55638390.0, '10/3/2005 16:00:00', '10/3/2005 15:45:00' union allselect 55638225.0, 55638240.0, '10/3/2005 18:30:00', '10/3/2005 17:30:00' union allselect 55638495.0, 55638510.0, '10/3/2005 20:30:00', '10/3/2005 20:15:00' union allselect 55638120.0, 55638660.0, '10/3/2005 23:00:00', '10/3/2005 14:00:00' union allselect 55638120.0, 55638660.0, '10/3/2005 23:00:00', '10/3/2005 14:00:00' union allselect 55639770.0, 55639830.0, '10/4/2005 16:00:00', '10/4/2005 15:45:00' union allselect 55639665.0, 55639680.0, '10/4/2005 18:30:00', '10/4/2005 17:30:00' union allselect 55639935.0, 55639950.0, '10/4/2005 20:30:00', '10/4/2005 20:15:00'select num2 - num1 as numdiff, datediff(minute, dt2, dt1 ) AS minutedifffrom @twitnumdiff minutediff------- ---------- 60 15 <-- No match. I think jobosco1988 switched these two line 15 60 <-- No match 15 15540 540540 540 60 15 <-- No match. I think jobosco1988 switched these two line 15 60 <-- No match 15 15 E 12°55'05.25"N 56°04'39.16" |
|
|
Next Page
|