SQL Select Statement Date Range issue

[choudhmh]

Hi guys,
I'm having problem writing an SQL statement where it will selcet records according to the date range submitted. Can someone help solve the issue listed below. I'm using MS access xp version with windows xp:

Dim strSQL AsString = "select * from StudentRegister WHERE DOB BETWEEN #2000/09/01 12:00:00 AM# AND #2001-09-31 11:59:59 PM#"

the error i keep on getting is:
Syntax error in date in query expression 'DOB BETWEEN #2000/09/01 12:00:00 AM# AND #2001-09-31 11:59:59 PM#'.

Mac

 

[renjivraman]

Dim strSQL AsString = "select * from RDATE where DOB between(' 1/1/1981 12:00:00 PM ') AND(' 8/6/2004 11:49:49 AM ')"i think a command text like this will sort out ur problem...

 

[cjard]

I suggest you get into the habit of using parameters. it will help you much across your coding life with databases:

Dim oleDBCmd = new OleDbCommand(existing connection)

oleDbCmd.CommandText = "SELECT * FROM table WHERE dateField BETWEEN ? and ?"
oleDbCmd.Parameters.Add("lowerDate", OleDbType.Date).Value = A_DATE_VARIABLE
oleDbCmd.Parameters.Add("upperDate", OleDbType.Date).Value = A_DATE_VARIABLE
'now look up how to use a command to fill a datatable or returna reader, there are many web examples

To re-run this query for a different range, simply change the parameters:

oldDbCmd.Parameters("lowerDate").Value = A_DIFFERENT_DATE

etc..

 

[cjard]

ps.. the order of the parameters addition matters very much! access doesnt do named parameters like sql server does, so the order of the ? marks is the order of the parameters addition!

 

[nl2ttl]

I have the same problem i have a table with the name:
transactie and a collum datum

No i want all the information on a sepcific date:

Why doesn't this work and how to do it right?

xConn.connectMe("SELECT * FROM transactie WHERE datum = '2005/10/11' ;")

Also i like to have al the information thats between two dates

 

[cjard]

OK, here's a really quick explanation.

What is this: '2005/10/11'

It is a STRING. You and me look at it and say "oh, it's a date" - but a computer isnt that artificially intelligent. Infact, I cant even be sure if thats the 10th of november or the 11th of october - how is a computer supposed to get it right?

The computer just sees it as a string, and it will either guess at converting it to a date (and maybe get it wrong) or guess at converting all the dates in the table to a string, and maybe get them wrong too. Cut out the giess work, use DATE parameters.

If youre using Access, then you can use the code i posted above. If youre not, then post more info about what you ARE using..

 

[nl2ttl]

oke but i uses this command:

xConn.connectMe("SELECT * FROM transactie WHERE datum = '2005/10/11' ;")

how can i convert the date?

 

[cjard]

Words fail me..

 

[pachjo]

Hi,

I have been having trouble with date ranges. I have my data returned at startup and all is well.

I then want the user to be able to select a range and amend the tableadapter accordingly.

This post appears to address the problem, but has introduced another problem which I don't understand?

With the code below I get an error stating syntax error near ?

glb_taTransactions.SelectCommand.CommandText = "SELECT t.id, t.tran_id, t.tran_date, td.tran_description, t.tran_amount, t.tran_balance, t.tran_save, t.tran_withdrawal " & _
                                                       "FROM transactions t INNER JOIN transaction_descriptions td ON t.tran_id = td.tran_id " & _
                                                       "WHERE tran_date BETWEEN ? AND ?"

glb_taTransactions.SelectCommand.Parameters.Add("from_date", SqlDbType.DateTime).Value = CDate(Me.dtmFromDate.Text)

glb_taTransactions.SelectCommand.Parameters.Add("to_date", SqlDbType.DateTime).Value = CDate(Me.dtmToDate.Text)

Any ideas?

 

[pachjo]

Have found out that this is a difference between MS Access and SQL Server.

I changed the ? to the @ named parameters and it works