number of days after 1900

[engnouna]

Hi
I have an SQL table that contains a field "Date" of type char,I've heard that there is a function that calculates the number of Days after the year 1900,
For example if I have the date 25/05/2006 and 06/06/2006 because Date is of char type,so it considered 25/05/2006 greater then 06/06/2006 what i want is to find the total number of days between 1900 and 25/05/2006 and the total number of days between 1900 and 06/06/2006 that way I can query my table correctly?

Can anyone tell me what's this function?

Thanks

[Static]

Well.. in VB you would use DateDiff

datediff("d",#1/1/1900#,#5/25/2006#)

[leinad31]

Hi
I have an SQL table that contains a field "Date" of type char,I've heard that there is a function that calculates the number of Days after the year 1900,
For example if I have the date 25/05/2006 and 06/06/2006 because Date is of char type,so it considered 25/05/2006 greater then 06/06/2006 what i want is to find the total number of days between 1900 and 25/05/2006 and the total number of days between 1900 and 06/06/2006 that way I can query my table correctly?

Can anyone tell me what's this function?

Thanks

If you had used a datetime data type (which is a numeric thats interpreted differently) then assigning it a value of zero gives you dec 30, 1899. Whole number values would be additional days from that base date, eg. , Jan 1, 1900 = 2.0. The fractional part would be a fraction of a day (hours minutes seconds). You can then perform arithmetic operations on the datetime data type (as long as you know what your doing and how to interpret the result).

Since your using char/string then your only option is to use DateDiff() as suggested (you just need to convert the string type field to datetime value). Unfortunately you have to exercise more care with interpreting string dates as opposed to datetime (numeric) representations. The string is interpreted based on PC settings for date format (with a few adjustments if that failed as is, eg. swapping mm with dd portion to try and get a valid date).

So to ensure the string is interpreted in the correct order for mm,dd and yy, regardless of the regional settings of the PC, you would create the datetime data type through DateSerial() rather than direct cast CDate(). You would then have to parse the string then use DateSerial() in your query.

Or just ensure that every pc this app will ever run on has its regional options for date format set accordingly.

[yulyos]

Hi,

Dim date1, date2 As Date
date1 = "11/06/50"
date2 = "11/06/06"
MsgBox "Days between date1 and date2:  " & DateDiff("d", date1, date2)

You can use the DateDiff function to determine how many specified time intervals exist between two dates. For example, you might use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the end of the year.

To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

If date1 refers to a later point in time than date2, the DateDiff function returns a negative number.

The firstdayofweek argument affects calculations that use the "w" and "ww" interval symbols.

If date1 or date2 is adate literal, the specified year becomes a permanent part of that date. However, if date1 or date2 is enclosed in double quotation marks (" "), and you omit the year, the current year is inserted in your code each time the date1 or date2 expression is evaluated. This makes it possible to write code that can be used in different years.

When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.

[bushmobile]

Hi,

Dim date1, date2 As Date
date1 = "11/06/50"
date2 = "11/06/06"
Dim d, d1, d2 As Double
d1 = Format(date1, "0")
d2 = Format(date2, "0")
d = d2 - d1
MsgBox "Days between date1 and date2:  " & d

you don't even need to do the conversion.

MsgBox date2 - date1

[engnouna]

Hi all
I think you misunterstood me,I have an SQL server table containing a field Date of char type, I tried to convert it to datetime type but it didn't work,so I think to calculate the number of days between each date and the year 1900,that way i can execute queries that retrieve records between 2 different days

Thanks

[Bregalad]

Btw, if you declare the variables like this:

Dim date1, date2 As Date

You end up with date1 as type variant, date2 as date.
If you want both variables to be of type date, you have to do this:

Dim date1 As Date, date2 As Date

[leinad31]

Hi all
I think you misunterstood me,I have an SQL server table containing a field Date of char type, I tried to convert it to datetime type but it didn't work,so I think to calculate the number of days between each date and the year 1900,that way i can execute queries that retrieve records between 2 different days

Thanks

Be it in the DB or not, you still have to convert your string/char to a numeric/datetime value.

Also, if not mistaken, your fieldname 'Date' is a reserved word.

[eimroda]

use CDate

CDate('25/05/2006')