fistaag
asked on
CDate Problem
Hi all
I have a serious problem with a sql-query using the CDate command on a Windows-System (german win 2000 server) using german Date Format. The Date locally cannot be changed to english short format, because of other programs.
The Dates in the Database are in US-Format: e.g. 01/30/2004
The Users enters a Form where he enters his date selection: e.g. from 02/10/2004 untill 04/30/2004. This dates are used to query the database with the following query:
SQL = "SELECT * FROM tblKundenstamm, tblVertrag " _
& "WHERE tblVertrag.strKSID = tblKundenstamm.IDRef AND tblKundenstamm.strFirma = '" & Request.Form("Kunde")
& "' AND CDate(strDate) BETWEEN #" & firstDate & "# AND #" & lastDate & "# " _
If the Date ist 12/20/2004 CDate converts it to 12.20.2004 which is correct. But if the Date looks like 02/10/2004 it is converted to 02.10.2004 which is not correct.
How can i correct this problem?
Thanks for your help
Fabian
I have a serious problem with a sql-query using the CDate command on a Windows-System (german win 2000 server) using german Date Format. The Date locally cannot be changed to english short format, because of other programs.
The Dates in the Database are in US-Format: e.g. 01/30/2004
The Users enters a Form where he enters his date selection: e.g. from 02/10/2004 untill 04/30/2004. This dates are used to query the database with the following query:
SQL = "SELECT * FROM tblKundenstamm, tblVertrag " _
& "WHERE tblVertrag.strKSID = tblKundenstamm.IDRef AND tblKundenstamm.strFirma = '" & Request.Form("Kunde")
& "' AND CDate(strDate) BETWEEN #" & firstDate & "# AND #" & lastDate & "# " _
If the Date ist 12/20/2004 CDate converts it to 12.20.2004 which is correct. But if the Date looks like 02/10/2004 it is converted to 02.10.2004 which is not correct.
How can i correct this problem?
Thanks for your help
Fabian
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ashutosh9910 can you post a example?
Dim d As Date
Dim str As String
str = "02/10/2004"
d = DateSerial(Mid(str, 7, 4), Mid(str, 1, 2), Mid(str, 4, 2))
Debug.Print d
Dim str As String
str = "02/10/2004"
d = DateSerial(Mid(str, 7, 4), Mid(str, 1, 2), Mid(str, 4, 2))
Debug.Print d
CDate(Format(strDate, "mm/dd/yyyy")) will make the CDate function read the first two digits as the month, the second two as the day and the last four as the year and convert it into the system format.
J.
J.
Format(CDate(strdate), "MM/dd/yyyy")
Be particular about using MM and not mm. This would cause you an error.
Be particular about using MM and not mm. This would cause you an error.
What's the data type of strDate ? Just always make sure the value of strDate is in mm/dd/yyyy format, try to avoid to use CDate function if possible.
Why is 12/20/2004 converted to 12.20.2004 correct but 02/10/2004 converted to 02.10.2004 incorrect?
JR
JR
ashutosh9910
>>Format(CDate(strdate), "MM/dd/yyyy")
>> particular about using MM and not mm. This would cause you an error.
Not in Format function in VB. m, mm = Months (except when following h or hh - then minutes) ; n, nn = minutes all the time.
MyDateVarString = Format(MyDate, "mm/dd/yyyy") ' is correct.
MyDateString = Format$(MyDate, "mm/dd/yyyy") ' is slightly more efficient.
>>Format(CDate(strdate), "MM/dd/yyyy")
>> particular about using MM and not mm. This would cause you an error.
Not in Format function in VB. m, mm = Months (except when following h or hh - then minutes) ; n, nn = minutes all the time.
MyDateVarString = Format(MyDate, "mm/dd/yyyy") ' is correct.
MyDateString = Format$(MyDate, "mm/dd/yyyy") ' is slightly more efficient.
12.20.2004 - means - December 20 2004 and
02.10.2004 - means - October 02 2004
if so then you can use Format function along with the CDate to convert it to your own desired format.