datepart returning wrong date

[MikeCallon]

Sorry if this is the wrong group but this is my first post - total newbie so please be gentle......

We have a SQL Server database where dates are stored as a float in a field called DT_StartDate (the dates are entered from a Visual Basic application running on various clients which use CDbl(Now) to convert the current date/time to a double).

If I create a VB executable and convert the value back to a date with CDate(DT_StartDate) I get the expected date.

If a do datepart(day,DT_StartDate) from query analyzer the day is 2 days out !

According to VB, the value 38104 is 27th April 2004

So in VB : CDate(38104) = 27/04/2004
SQL Server : SELECT datepart(day,38104) = 29 !!!!!

Anyone had a similar problem ?

I'm not sure why the dates are stored as float (I inherited this problem) - is this the cause of the problem ?

Any pointers would be greatly appreciated.

Cheers,

Mike

 

[swampBoogie]

where dates are stored as a float

Use datetime for storing date and time values.

 

[donutman]

If you are stuck with using a float field, then double check that the 2 day differential is maintained at the beginning and end of time (for your needs). If so then ADJUST. This sort of thing has happened with QBasic, Excel and SQL before. There's no absolute zero when it comes to dates!
-Karl

 

[MikeCallon]

Thanks for the replies.

We're stuck with doubles - this is a large legacy database - there are a lot of applications reading the data - I can't change the data type without loads of potential problems.

I'm not looking forward to breaking the news that their date calculations maybe incorrect - if the applications have been written to use the built in CDate() function in VB they're ok - if the applications use the SQL server datepart() function then the dates are out by 2 days - I don't have any control over how individual programmers have developed their applications !

Is there a setting in SQL Server I can change to fix the problem.

VB calculates dates by counting the number of days since 1st January 1900 - hence 38104 is 27th April 2004.

I thought SQL Server did the same - is this not correct ?

If not where/how can I change it ?

Many thanks,

Mike

 

[vongrunt]

AFAIK there are no such settings in SQL Server.

Besides, why VB/ASP CDate(2) returns 1st Jan 1900? At least to me...

 

[MikeCallon]

vongrunt,

You're right about the date in VB (should check things out myself rather than relying on second hand info next time eh ?)

I guess we have to try and fix the way that VB converts the current date to a double so that it matches SQL Servers (seemingly correct) interpretation of the date.

Many thanks for the wise up.

Mike