SQL Date Format 1

[Prof63]

Hi

I have an input box with input mask for two dates as dd/mm/yyyy which I pass as variables to several SQL queries in VBA. When the date is, for example, 17th November the date is passed as 11/17/2002 (correct for SQL) but 1st November is passed as 01/11/2002. Both dates are treated in the same way using the following

datStart = Format(txtStartDate.Text, "DD/MM/YYYY&quot
datEnd = Format(txtEndDate.Text, "DD/MM/YYYY&quot

I have tried passing as strings

strStartDate = left(datStart,2) & "/" & mid(datStart,4,2) & "/" & right(datStart,4)

but I still can't get dates before the 12th of the month to pass correctly. What simple thing am I doing wrong???

Thanks for your help

 

[cmmrfrds]

If the date is a date/time data type passed to the query it should not make a difference how the text box was formatted, since a date/time will be passed correctly as
'11/17/2002' or '11/01/2002' and this is the correct format for the sql statement.


Dim begdate as datetime
dim enddate as datetime
dim sql as string

sql = "Select * from yourtable where yourdate between '" & _
begdate "' and '" & enddate & "'"

 

[keimpe]

Hi there,

if you create your SQL string in VBA, don't forget to include the # around the date, like: #11/01/2002#

To see how this works, simply create a query in query design view that selects records based on a certain date and then switch to SQL view. Then you'll see the 'behind-the-scenes' syntax. This is the syntax that you'll have to use in VBA code.

Greetings!
Keimpe

 

[Prof63]

Keimpe

What about if I'm passing a variable, such as datStartDate?

 

[keimpe]

Hi,

if you pass that variable to an existing query, than there's no problem.

If you use that variable to create an SQL string in code, I suggest you feed it to a function that converts this variable into a date with the right syntax to be used in a query.

I use the following function:

Function DateSQL(vDate As Variant) As String
sDateSQL = "#" & Format$(vDate, "mm&quot & "/" & Format$(vDate, "dd&quot & "/" & Format$(vDate, "yyyy&quot & "#"
DateSQL = sDateSQL
End Function

And I use this function as follows:

strSQL = "SELECT * FROM ORDERS WHERE ORDERDATE = " & DateSQL(datStartDate) & " ORDER BY ORDERNUMBER;

Greetings,
Keimpe

 

[DonQuichote]

There's a lot of dangerous thing going on here. In the original post, you converted between date and string about 4 times! No wonder things go wrong.

Let's see:

datStart = Format(txtStartDate.Text, "DD/MM/YYYY")

the text property of a textbox is a string. You do a date conversion, so it is converted to a date first(1), the format function converts it back to a variant/string(2). It is then assigned to a date variable, so it is again converted to a date(3). To use it in an SQL string, you have to do a string conversion again(4).

To denote a date properly, without the regional settings getting in the way, you have to use a date literal: an american style data (MM/DD/YYYY) between number signs. If you pass it in an SQL string, you'd use something like:

"#" & format$(datStart,"MM/DD/YYYY")& "#"

. Note that this is VBA only. It does not necessarily work for pass-through queries to database servers.

To do it properly with a text box:
on the exit event of the textbox, you can check if the text is a date (use the IsDate function) and if it is, store this text (with CDate if you like) in a date variable. Then set the text property to the "long date" format$ of the date variable, so the user knows how the text is interpreted.
You can now store the date variable in a date field of a table without any losses.

Best regards

 

[keimpe]

Hi DonQuichote,

thanks for correcting me!

Keimpe

 

[Prof63]

Thanks for the feedback.

Just to confirm I have understood whats being said...

I don't need to do anything in the SQL statement other than enclose the variable (string from textbox) with # ie

......between #" & datStart & "#.....

I have an input mask on the textbox such that the input must be dd/mm/yyyy but presumably I'd need to change this to mm/dd/yyyy?