VB Date Format

VB uses two different date locales - the system locale that determines the output format of the date, and the code locale, which is ALWAYS U.S. English. Using the "#" delimiter (as recommended by MSDN) forces the date to be interpreted as the code locale - U.S. English, and thus mm/dd/yyyy; the system locale has no influence on this.

However, VB tries to be helpful when interpreting an entered date, and this can cause no end of trouble and confusion. For example:

#18/04/2002# is interpreted as 18th April 2002

#04/18/2002# is also interpreted as 18th April 2002

#08/04/2002# is interpreted as 4th August 2002

It seems that the date is taken as mm/dd/yyyy when it makes sense, otherwise VB assumes that it's dd/mm/yyyy, and does it's own format conversion behind the scenes. This problem is compounded when dates are passed to SQL Server as part of an embedded SQL statement.

It is far, far better to ignore the MSDN advice, and pass dates as SQL character strings, always pre-formatted to the desired date format. For example:

"Where startdate > '" & format$(Now, "mm/dd/yyyy") & "' "

Note that the date string MUST be enclosed in single quotes, as above.

This can still cause problems, though, depending on the date format that your particular SQL Server installation is expecting. One solution is to include a "SET DATEFORMAT mdy" statement at the start of the Select statement. A better solution is to always format the date in a way that is not open to misinterpretation by SQL Server; dates such as 2002-08-04 and 04 Aug 2002 seem to be translated OK irrespective of the date format settings in either VB or SQL Server.

So, the above "Where" statement could be coded as either:

"Where startdate > '" & format$(Now, "yyyy-mm-dd") & "' "

or

"Where startdate > '" & format$(Now, "dd mmm yyyy") & "' "

Both of these will work as expected.

As an aside, I don't think that it's a good idea to use "short date" and "long date" as part of a format$ function, where the formatted dates are being used as part of a SQL statement, as these formats can be defined differently on different machines. Thus, a piece of code might work fine on machine A, but either crashes or produces incorrect results on machine B.

 http://www.vb-helper.com/bug_sql_dates.html

 My Problem:

I have one stored procedure and one view.

View :

“SELECT * FROM [dbo].[vwOutstandingHBVBSInstructions] WHERE [Instruction Received] >= '" & Format$(txtFromDate.Value, "mm/dd/yyyy") & "'" & " AND [Instruction Received] <= '" & Format$(txtToDate.Value, "mm/dd/yyyy") & "'"

Its working fine without any problem.

If I use the same date format mm/dd/yyyy while passing date parameters to stored procedures it is changing the format to dd/mm/yyyy(Initially i am very confused and couldn’t figure out the problem until i used SQlProfile to test the parameters it is passing to the stored procedure and view).

    Dim comm As New ADODB.Command

    comm.ActiveConnection = conn


    comm.CommandText = "spMyProc"


    comm.CommandType = adCmdStoredProc


    comm.Parameters.Append comm.CreateParameter("@FromDate", adDBDate, adParamInput, 100, Format$(txtFromDate.Value, "mm/dd/yyyy"))


    comm.Parameters.Append comm.CreateParameter("@ToDate", adDBDate, adParamInput, 100, Format$(txtToDate.Value, "mm/dd/yyyy"))


    Set objRS = comm.Execute

this format is giving error.

So i have to changed it to ‘dd/mm/yyyy’