Date Time Conversion - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Date Time Conversion

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 EXPR1
FROM dbo.DET_SEG

Example

1/4/2002 12:00:00 AM

jbosco1988
Starting Member

46 Posts

Posted - 2007-08-22 : 13:04:53

I keep gettin the Arithmetic overflow error

SQLSRv32.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.

Example

1/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.0

SELECT 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 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 12:32:11

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"

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.997

CODO 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 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[/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 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)

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	@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"

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources