Zero Dates Differ!?

[Kev]

I am using VB6 accessing as SQL Server 2000 Database.

In my VB app I convert my dates to longs and store themin the SQL Server DB.

I thas now come to attention that the zero date in VB6 is 30/12/1899, but in SQL Server 2000 it is 01/01/1900.


Is there a way to set the zero date in VB6 so that it matches the SQL Server DB?




Kev.

[DeadEyes]

why do you want a zero date?
surely on insert if the value is zero you should set the date to null.
If nulls are not allowed can you convert to date you get from SQL to a long and if it's zero set the date to the one you want 01/01/1900

[Kev]

It's not that i Want a zero date.

I convert a date in VB to a long number...e.g. 01/02/2004 = 38106 (it doesn't really equal that, this is just an example)

When you convert 38106 to a date in SQL Server 2000 it equals 03/02/2004.

They are not the same.

The easy way out is to subtract 2 in SQL Server. But why aren't they the same...they are both Microsoft Products.

I was wanting to know if there was a way to reset the date in VB so the 38106 converts to the same date in both products.

[DeadEyes]

I've never come accross this problem myself. Is SQL Server on another machine maybe the date settings are different American/UK.

[Kev]

Unfortunately not....I checked the SQL Server machine for that.

It's a puzzle.

[si_the_geek]

Why are you using longs to store dates?

If you keep them as dates then there is not an issue, as the appropriate conversion will we done for you (no matter what the database date settings are).

[Kev]

I found it easier to do comparisons....you don't have to worry about what format the date is in.

[crptcblade]

Originally posted by Kev
I found it easier to do comparisons....you don't have to worry about what format the date is in.

The format shouldn't matter if you use Date types.

[Kev]

I'm coming from an access background...and when doing date comparisons then the format mattered.

[si_the_geek]

The format matters when you put it into a database and get it out again (for which you can use the Format function), but otherwise you should always work with the Date data type.

Different databases will have different "base" dates. If you carry on with what you have and ever decide to change databases, you will have to edit all the code that puts/gets dates from the database.

[vbmom]

I'm not sure where your conversion from a julian long date to the datetime value is, but in SQL server the smalldatetime datatype values range: January 1, 1900, through June 6, 2079. The datetime datatype ranges from January 1, 1753 through December 31, 9999.

SQL help says:

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

Regarding your comment about it being easier to handle this way: From my experience, no matter how you slice it, you're going to have to do work to handle dates. I do think you are better off letting SQL server manage dates for you, but yes, you have to explore the world of login connections where the default language determines date format (mdy, dmy, ymd) or make sure you use SET DATE FORMAT before every SQL execute. After all, what are we paying Mr. Gates for anyway?

[longwolf]

Here's a thought.
Store them as dates then convert them for your comparisons.