How to write Date in SQL

  • Thread starter Thread starter Wahab
  • Start date Start date
W

Wahab

Hi,
I want to extract records from table between 2 dates.
Beginning and EndingDate will come from the Form which is
having two fields, BeginningDate and EndingDate, and one
Command Button.
I will input require dates, on click of button I wrote
update sql which will go and update the records, which I
extracted from SQL. Here I don't want to create query, I
want to extract records without query. Please let me know
how I will write Date Sql Based on form which has to date
fields.
 
Use Criteria expression similar to this for the date field in the query:

Between [Forms]![FormName]![StartDateControlName] And
[Forms]![FormName]![EndDateControlName]
 
Wahab said:
Hi,
I want to extract records from table between 2 dates.
Beginning and EndingDate will come from the Form which is
having two fields, BeginningDate and EndingDate, and one
Command Button.
I will input require dates, on click of button I wrote
update sql which will go and update the records, which I
extracted from SQL. Here I don't want to create query, I
want to extract records without query. Please let me know
how I will write Date Sql Based on form which has to date
fields.
Click to expand...

"SELECT * FROM TableName WHERE YourData Between #" & Me.BeginningDate & "#
And #" & Me.EndingDate & "#"

If the two TextBox Control (BeginningDate and EndingDate) are not located in
the same Form
where you have Command Button you need to Explicit the Location Form like
this
Forms!FormName!BeginningDate

Bye.
@Alex.
 
"SELECT * FROM TableName WHERE YourData Between #" & Me.BeginningDate
& "# And #" & Me.EndingDate & "#"
Click to expand...

This will fail horribly and spectacularly (a) if there are any Time Values
in the DateTime values, and (b) anywhere outside N America.

The criterion should run something like

" ... WHERE DATEVALUE(YourData) BETWEEN " & _
Format$(dtBeginningDate, "\#yyyy\-mm\-dd\#") & " AND " & _
Format$(dtEndingDate, "\#yyyy\-mm\-dd\#") & " ... "

The correct handling of Date Literals in SQL expressions is explained in
the help files.

B Wishes


Tim F
 
Tim Ferguson said:
This will fail horribly and spectacularly (a) if there are any Time Values
in the DateTime values, and (b) anywhere outside N America.

The criterion should run something like

" ... WHERE DATEVALUE(YourData) BETWEEN " & _
Format$(dtBeginningDate, "\#yyyy\-mm\-dd\#") & " AND " & _
Format$(dtEndingDate, "\#yyyy\-mm\-dd\#") & " ... "

The correct handling of Date Literals in SQL expressions is explained in
the help files.

B Wishes


Tim F
Click to expand...


I'm sure that in USA is right your sintax , but in ITALY i'm also sure is
right and Work
good.... also mine...if my sintax is writed on QRY(QBE) compiled.
In this way no Format action are necessary....!!!! If you use SQL by
recordset extraction your
thing is right....!
So no horribly and spectacularly Failure is happened...! which
catastrophe....!!! ;-)
You could give more tecnical support explaining in a better way.., your
replay has been a little hard one
but thanks for the clarity.....!

Alessandro(IT)
 
I'm sure that in USA is right your sintax , but in ITALY i'm also sure
is right and Work
good.... also mine...if my sintax is writed on QRY(QBE) compiled.
In this way no Format action are necessary....!!!!
Click to expand...

Okay: look at this. In a plain date-to-text coercion as in

"... #" & SomeDate & "# ..."

VBA will use the International Settings to make the conversion - thus in
UK, Italy, and most of the 90% of the world that use d/m/y this will give

" ... #11/01/2004# ... "

while in the USA and the rest of the 4% of the world that uses m/d/y, it
will read

" ... #01/11/2004# ... "

Now, Jet will see the first date as November and the second one as January.
Regardless of the local machine's international settings. Simple text
coercion is not safe in an international context and will give incorrect
results. This is well documented in the help files, and I suggest you check
up on this, particularly if you are developing on a m/d/y machine and may
be handing your work over to colleagues using d/m/y.

Another point. Be aware that the Query Designer will take any date you type
in and switch it behind the scenes into a m/d/y date -- look at the actual
SQL view of the query --, and the Expression Engine will equally intercept
any GUI-based queries and fiddle about, usually getting you the right
answer. Once you get into real programming, though, both of these safety
nets disappear, and what you type is what Jet sees. That is when most of
these international date-type bugs bite.

Hope that makes it a bit clearer.


Tim F
 
Back
Top