SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Date Time Conversion
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

jbosco1988
Starting Member

USA
46 Posts

Posted - 08/22/2007 :  12:38:21  Show Profile  Send jbosco1988 a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 08/22/2007 :  12:54:50  Show Profile  Reply with Quote
select DATEADD(minute,53518755.0 ,0). I am assuming 53518755.0 is the number of minutes since '01/01/1900'

Jim
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/22/2007 :  12:55:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It depends.
What date (and time) is 53518755.0 supposed to respresent?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/22/2007 :  12:56:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jbosco1988
Starting Member

USA
46 Posts

Posted - 08/22/2007 :  13:01:44  Show Profile  Send jbosco1988 a Yahoo! Message  Reply with Quote
SELECT CONVERT(smalldatetime, START_MOMENT) AS EXPR1
FROM dbo.DET_SEG

Example

1/4/2002 12:00:00 AM
Go to Top of Page

jbosco1988
Starting Member

USA
46 Posts

Posted - 08/22/2007 :  13:04:53  Show Profile  Send jbosco1988 a Yahoo! Message  Reply with Quote
I keep gettin the Arithmetic overflow error

SQLSRv32.dll
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 08/22/2007 :  13:26:35  Show Profile  Reply with Quote
Please do tell us what 53518755.0 is. Is it minutes from '01/01/1900'? We're dying to know.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/22/2007 :  13:29:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Maybe you just could please stress down, and answer our questions first?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jbosco1988
Starting Member

USA
46 Posts

Posted - 08/23/2007 :  11:58:44  Show Profile  Send jbosco1988 a Yahoo! Message  Reply with Quote
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.

Example

1/4/2002 12:00:00 AM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/23/2007 :  12:05:21  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 08/23/2007 :  12:06:09  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/23/2007 :  12:20:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
declare @i decimal(9,1)
set @i = 53518755.0

SELECT	REPLACE(STR(@i, 9, 0), ' ', '0'),
	STUFF(STUFF(STUFF(REPLACE(STR(@i, 9, 0), ' ', '0'), 7, 0, '.'), 5, 0, ':'), 3, 0, ':')



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jbosco1988
Starting Member

USA
46 Posts

Posted - 08/23/2007 :  12:23:07  Show Profile  Send jbosco1988 a Yahoo! Message  Reply with Quote
First Jim I have answered you question.

Michael Valentine Jones

Here 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.0

Here 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 Start
280001 10/3/2005 4:00:00 PM 10/3/2005 3:45:00 PM
280001 10/3/2005 6:30:00 PM 10/3/2005 5:30:00 PM
280001 10/3/2005 8:30:00 PM 10/3/2005 8:15:00 PM
280001 10/3/2005 11:00:00 PM 10/3/2005 2:00:00 PM
280001 10/3/2005 11:00:00 PM 10/3/2005 2:00:00 PM
280001 10/4/2005 4:00:00 PM 10/4/2005 3:45:00 PM
280001 10/4/2005 6:30:00 PM 10/4/2005 5:30:00 PM
280001 10/4/2005 8:30:00 PM 10/4/2005 8:15:00 PM
280001 10/4/2005 11:00:00 PM 10/4/2005 2:00:00 PM
280001 10/4/2005 11:00:00 PM 10/4/2005 2:00:00 PM
280001 10/5/2005 4:00:00 PM 10/5/2005 3:45:00 PM
280001 10/5/2005 6:30:00 PM 10/5/2005 5:30:00 PM
280001 10/5/2005 8:30:00 PM 10/5/2005 8:15:00 PM
280001 10/5/2005 11:00:00 PM 10/5/2005 2:00:00 PM
280001 10/5/2005 11:00:00 PM 10/5/2005 2:00:00 PM
280001 10/6/2005 4:00:00 PM 10/6/2005 3:45:00 PM
280001 10/6/2005 6:30:00 PM 10/6/2005 5:30:00 PM
280001 10/6/2005 8:30:00 PM 10/6/2005 8:15:00 PM
280001 10/6/2005 11:00:00 PM 10/6/2005 2:00:00 PM
280001 10/6/2005 11:00:00 PM 10/6/2005 2:00:00 PM




Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/23/2007 :  12:32:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are the numbers sorted?

First row
280001 55638330.0 55638390.0

Represents
280001 10/3/2005 3:45:00 PM 10/3/2005 4:00:00 PM

I am just wondering because you posted 19 numeric value and 20 datetime values.



E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 08/23/2007 12:35:49
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/23/2007 :  12:35:54  Show Profile  Reply with Quote
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.997




CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/23/2007 :  12:38:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Please post the CDATE solution you have in ACCESS today.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/23/2007 :  12:44:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 08/23/2007 13:39:36
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/23/2007 :  12:51:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Prepare sample data
declare	@agent table (AgentID int, Start money, Stop money)

insert	@agent
select	280001, 55638330.0, 55638390.0 union all 
select	280001, 55638225.0, 55638240.0 union all
select	280001, 55638495.0, 55638510.0 union all
select	280001, 55638120.0, 55638660.0 union all
select	280001, 55638120.0, 55638660.0 union all
select	280001, 55639770.0, 55639830.0 union all
select	280001, 55639665.0, 55639680.0 union all
select	280001, 55639935.0, 55639950.0 union all
select	280001, 55639560.0, 55640100.0 union all
select	280001, 55639560.0, 55640100.0 union all
select	280001, 55641210.0, 55641270.0 union all
select	280001, 55641105.0, 55641120.0 union all
select	280001, 55641375.0, 55641390.0 union all
select	280001, 55641000.0, 55641540.0 union all
select	280001, 55641000.0, 55641540.0 union all
select	280001, 55681590.0, 55681650.0 union all
select	280001, 55681485.0, 55681500.0 union all
select	280001, 55681755.0, 55681770.0 union all
select	280001, 55681380.0, 55681920.0

-- Show the expected output
SELECT		AgentID,
		Start, 
		Stop,
		dateadd(minute, start, '18991220 21:00:00'),
		dateadd(minute, stop, '18991220 21:00:00')
from		@agent
order by	agentid,
		start



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jbosco1988
Starting Member

USA
46 Posts

Posted - 08/23/2007 :  13:06:41  Show Profile  Send jbosco1988 a Yahoo! Message  Reply with Quote
Michael Valentine Jones

55638330.0 55638390.0 10/3/2005 4:00:00 PM 10/3/2005 3:45:00 PM
55638225.0 55638240.0 10/3/2005 6:30:00 PM 10/3/2005 5:30:00 PM
55638495.0 55638510.0 10/3/2005 8:30:00 PM 10/3/2005 8:15:00 PM
55638120.0 55638660.0 10/3/2005 11:00:00 PM 10/3/2005 2:00:00 PM
55638120.0 55638660.0 10/3/2005 11:00:00 PM 10/3/2005 2:00:00 PM
55639770.0 55639830.0 10/4/2005 4:00:00 PM 10/4/2005 3:45:00 PM
55639665.0 55639680.0 10/4/2005 6:30:00 PM 10/4/2005 5:30:00 PM
55639935.0 55639950.0 10/4/2005 8:30:00 PM 10/4/2005 8:15:00 PM

Peso

Access
Stop: CDate([STOP_MOMENT]/1440)
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 08/23/2007 :  13:07:10  Show Profile  Reply with Quote
And Peso gets the cheese!


Jim
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/23/2007 :  13:25:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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	@twit
select	55638330.0, 55638390.0, '10/3/2005 16:00:00', '10/3/2005 15:45:00' union all
select	55638225.0, 55638240.0, '10/3/2005 18:30:00', '10/3/2005 17:30:00' union all
select	55638495.0, 55638510.0, '10/3/2005 20:30:00', '10/3/2005 20:15:00' union all
select	55638120.0, 55638660.0, '10/3/2005 23:00:00', '10/3/2005 14:00:00' union all
select	55638120.0, 55638660.0, '10/3/2005 23:00:00', '10/3/2005 14:00:00' union all
select	55639770.0, 55639830.0, '10/4/2005 16:00:00', '10/4/2005 15:45:00' union all
select	55639665.0, 55639680.0, '10/4/2005 18:30:00', '10/4/2005 17:30:00' union all
select	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 minutediff
from	@twit


numdiff	minutediff
-------	----------
 60	 15		<-- No match. I think jobosco1988 switched these two line
 15	 60		<-- No match
 15	 15
540	540
540	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"
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000