[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

DateTime Problem

Praveen

3/29/2007 5:28:00 PM

We're having a problem with extracting date information from a MS SQL
DB. The 'earliestdispatch' column is defined as FLOAT size 53.
It has a value in the table as 39160

When I display it as: cast(earliestdispatch as datetime), it converts
it to 03/21/07

When I displayed it as DATEDIFF([MI], EarliestDispatch, GETDATE()),
Run today, I get 11776.

When you subtract this from getdate(), you get 03/21/07.

However, in the application which uses it in a more GUI form, it shows
it as the same time, but two days earlier (03/19/07)

I tried to convert the number 39160 to a date time value in excel
sheet, I got the same value (03/19/07). I got puzzled.

Why SQL server date time and Excel date time functions are behaving
differently? If so why my application is behaving differently.

Here's the SQL that is being executed from GUI

select EarliestDispatch DbEarliestDispatch,
CAST(EarliestDispatch as datetime) CastEarliestDispatch,
DATEDIFF([MI], EarliestDispatch, GETDATE()) DateDiffEarliestDispatch
from mainroute
where id='IGM027' and groupid='0319GRI1'
2 Answers

Tibor Karaszi

3/29/2007 5:31:00 PM

0

Why do you store datetime as float? That is asking for problems like this. Different product use
different "base dates" for their internal representation, and this is what you see here...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Praveen" <apveen@gmail.com> wrote in message
news:1175189252.394306.232450@o5g2000hsb.googlegroups.com...
> We're having a problem with extracting date information from a MS SQL
> DB. The 'earliestdispatch' column is defined as FLOAT size 53.
> It has a value in the table as 39160
>
> When I display it as: cast(earliestdispatch as datetime), it converts
> it to 03/21/07
>
> When I displayed it as DATEDIFF([MI], EarliestDispatch, GETDATE()),
> Run today, I get 11776.
>
> When you subtract this from getdate(), you get 03/21/07.
>
> However, in the application which uses it in a more GUI form, it shows
> it as the same time, but two days earlier (03/19/07)
>
> I tried to convert the number 39160 to a date time value in excel
> sheet, I got the same value (03/19/07). I got puzzled.
>
> Why SQL server date time and Excel date time functions are behaving
> differently? If so why my application is behaving differently.
>
> Here's the SQL that is being executed from GUI
>
> select EarliestDispatch DbEarliestDispatch,
> CAST(EarliestDispatch as datetime) CastEarliestDispatch,
> DATEDIFF([MI], EarliestDispatch, GETDATE()) DateDiffEarliestDispatch
> from mainroute
> where id='IGM027' and groupid='0319GRI1'
>


Saga

3/29/2007 7:31:00 PM

0

I agree with Tibor!

SQL Server and VB(A) apps store numeric equivalent of date differently.

In your case, what is date 0?

For SQL Server, it is Jan 1 1900
For VB(A) it is Dec 30, 1899

There is your 2 day discrenpancy and why Tibor is 110% correct!

Saga
--



"Praveen" <apveenNON@SPAMMEgmail.com> wrote in message
news:1175189252.394306.232450@o5g2000hsb.googlegroups.com...
> We're having a problem with extracting date information from a MS SQL
> DB. The 'earliestdispatch' column is defined as FLOAT size 53.
> It has a value in the table as 39160
>
> When I display it as: cast(earliestdispatch as datetime), it converts
> it to 03/21/07
>
> When I displayed it as DATEDIFF([MI], EarliestDispatch, GETDATE()),
> Run today, I get 11776.
>
> When you subtract this from getdate(), you get 03/21/07.
>
> However, in the application which uses it in a more GUI form, it shows
> it as the same time, but two days earlier (03/19/07)
>
> I tried to convert the number 39160 to a date time value in excel
> sheet, I got the same value (03/19/07). I got puzzled.
>
> Why SQL server date time and Excel date time functions are behaving
> differently? If so why my application is behaving differently.
>
> Here's the SQL that is being executed from GUI
>
> select EarliestDispatch DbEarliestDispatch,
> CAST(EarliestDispatch as datetime) CastEarliestDispatch,
> DATEDIFF([MI], EarliestDispatch, GETDATE()) DateDiffEarliestDispatch
> from mainroute
> where id='IGM027' and groupid='0319GRI1'
>