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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

SwePeso
Patron Saint of Lost Yaks

Sweden
30175 Posts

Posted - 08/23/2007 :  13:29:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I was little too hasty since there was no proper sample data.
This, however, finally sorted things out. Base date is incredibly Dec 21, 1899.
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	*,
	case when num2 - num1 = datediff(minute, dt2, dt1) then 'proper sample data' else 'sigh...' end,
	dateadd(minute, num2, -11) as newdt1,
	dateadd(minute, num1, '18991221') as newdt2
from	@twit
If you look at the pair of records that are not proper sample data, you can see that you get exactly the wanted result when shifting them.


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

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

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 08/23/2007 :  13:49:40  Show Profile  Reply with Quote
I bet the base date was the real answer he was looking for, that's why he never answered the question of what is 55638330?

Jim
Go to Top of Page

jbosco1988
Starting Member

USA
46 Posts

Posted - 08/23/2007 :  14:50:24  Show Profile  Send jbosco1988 a Yahoo! Message  Reply with Quote
Ok I give up. Jimf I gave the the answer fives times. I tried my best to line them up. It is difficult in this editor. Sorry to bother all you. But I thought that was what this forum was for.
Go to Top of Page

jbosco1988
Starting Member

USA
46 Posts

Posted - 08/23/2007 :  14:51:44  Show Profile  Send jbosco1988 a Yahoo! Message  Reply with Quote
Also thanks Peso for understanding.
Go to Top of Page

jbosco1988
Starting Member

USA
46 Posts

Posted - 09/04/2007 :  11:06:36  Show Profile  Send jbosco1988 a Yahoo! Message  Reply with Quote
Actually if you are going to be such a jerk Peso, No I dont want your help. As for sloppy work, you got the point. How many examples did you need, you know what i was trying to accomplish. All I did is copy and paste. It is difficult to lines things up in this editor.

Thanks,

James Bosco
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30175 Posts

Posted - 09/04/2007 :  11:13:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Hey! Welcome back! What brought you back after almost 2 weeks from last time?

1) All I needed was one proper sample data.
2) No, I had no idea what you were trying to accomplish. I had to do all the research myself.

But you are welcome with your excellent feedback.
You are right about I'm not being your personal tutor. The question remains if anyone else will.


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

EDIT: Typo

Edited by - SwePeso on 09/04/2007 13:28:29
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/04/2007 :  11:40:01  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
James -- why don't you go back and re-read your questions and the responses. No one is concerned about the *formatting* of your sample data, it is that it was NOT ACCURATE and did not help us to understand what you are looking for, and you NEVER specified what you need precisely and accurately. Look at Peso's post at the very top of the second page, he used your exact data and it is not accurate -- how can this help us to find a solution for you??

Peso spent a lot of time using trial and error to guess what you needed, a lot of time that was unnecessary and he certainly isn't being paid for. Or, in your case, even thanked for ...

What if I ask you a very simple question:

"Please help me fix these numbers! I have

1
2
3
4

and I would like them to be like this:

3
4
7

Thanks!"

would you have any idea what I am asking for? Do you know what I need? It is very simple what I am after, very basic, but if I don't explain what I need and/or provide clear, accurate sample data and results, it is impossible to know what I am looking for!

Does this make sense?

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 09/04/2007 11:42:59
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 09/04/2007 :  14:27:56  Show Profile  Reply with Quote
Heck, I just wanted to know what the units were. JBosco says he told me five times, but he must not be using base 10, because I only count 0 times.

Jim

P.S. There are only 10 types of people in the world, those that understand binary numbers and those that don't!
Go to Top of Page

jbosco1988
Starting Member

USA
46 Posts

Posted - 09/04/2007 :  18:37:21  Show Profile  Send jbosco1988 a Yahoo! Message  Reply with Quote
No I dont know everything. I never said I did. That is why I came here in the first place. Sorry to waist your time. I came here to ask a question and all I got was capped on when I did not give you exactly what you asked for. I never said I was perfect. I am sorry I am not as inteligent as you all seem to be. Dont worry I will not come here and aske any more questions. Sorry, I thought this was a forum for people learning as well as know allot.

Thanks,

James Bosco
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30175 Posts

Posted - 09/05/2007 :  03:37:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Capped? No one here at SQLTeam nor in this thread assumes you know everything.
Here are some very clever people willing to assist you as long as you help us.

All we did was ask you to give us PROPER sample data. Many times as I recall.
We asked nice and polite.

You know, all a date really is, is an offset to a certain point in time! So if you give us a number 13446576 we have absolutely no idea of what that is. No matter if you also give us the calendar interpretation of that number.
How shall we interpret that? Number of minutes passed since a certain fixed base date? Number of hours?
You tell us! But you didn't, even if we asked you to.
Here is the point were people started to loose interest in your problem. Because of you!

We don't have to help you. But we do, because we like to help and educate people like you to excel and learn.

BUT..! If you had given us another reference point, 14579466, and the calendar interpretation that is supposed to represent, it is very easy to calculate the differences in numbers and compare them with the differences in calendar representations. Now we can compare the time interval between them!

But in your case, there sometimes (about 50% of the sample data) was a difference of 3 hours between the numbers and the calendar representations, because of you not being tidy when posting them. It has absolutely nothing to do with formatting! Please understand this.
And that's why it took much longer time than usual to solve this case.

It seems that a large step in your learning process is to comply when asked to give some proper sample data, and you being willing to provide the material we need to help you.
Stop excuse yourself.

Until you overcome your issues, you will have same problems in the future.

Good luck.


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

Edited by - SwePeso on 09/05/2007 04:04:43
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/07/2007 :  13:35:49  Show Profile  Reply with Quote
quote:
Originally posted by jbosco1988

I did not give you exactly what you asked for.




Ummmmmmm

Why Not?

Maybe that's a good place to start.

If you don't understand what's being asked of you, maybe you should ask questions about that first.

All these guys are doing is trying to help you, help them

Pretty simple

Oh, and look at the hint link in my sig below....AND Read it



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/07/2007 :  13:43:55  Show Profile  Reply with Quote
And guys he doesn't know about [ code] [ /code] tags

And noone told him/her



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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