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
Who is Participating?

        CDate(Format(strDate, "mm.dd.yyyy")


Ashutosh VyasFounder, InitQubeCommented:
may be i did not get your point is it like
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.
fistaagAuthor Commented:
ashutosh9910 can you post a example?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
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.

Ashutosh VyasFounder, InitQubeCommented:
Format(CDate(strdate), "MM/dd/yyyy")

Be particular about using MM and not mm. This would cause you an error.
Ryan ChongCommented:
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?

>>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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.