SQL Querie Question

S

SonicBoomAu

Well-known member
Joined
Oct 30, 2003
Messages
179
Location
Australia
Hi All,

I am trying to search through a database and select items which meet the follow statement.

item signout between date1 and date2 (ie. 10 jan 05 - 20 mar 05) and where signin dates is between date1 and date2.

I have two columns called SignInDate and SignOutDate.

So far my statement is as follows.

[VB]
strNewSQLStatement = "SELECT * From IssueHistory WHERE " & _
SignOutDate <= strDVDate1 And _
SignInDate >= strDVDate2
[/VB]
 
I didnt see a question...? Just bragging, or looking for help?

Do you need help with the SQL, or building the string, or making the DB call with ADO.NET? Or...?

-ner
 
[VB]
strNewSQLStatement = "SELECT * From IssueHistory WHERE " & _
(SignOutDate between strDVDate1 And strDVDate2) and
(SignInDate between strDVDate1 And strDVDate2)
[/VB]

Most SQL dialects accepts "between" AFAIK

HTH
/Kejpa
 
Hi Nerseus,

Need help with SQL Statement.
Im getting a "Syntax error (missing operator) in query expression SignOutDate => 10/12/2004 And SignOutDate =< 10/12/2005".

Im just not sure about the syntax with the where part of the statement.

[VB]
strNewSQLStatement = "SELECT * From IssueHistory WHERE " & _
strSelectedSearchTypeDate1 & " => " & CDate(strDVDate1) & " And " & _
strSelectedSearchTypeDate2 & " =< " & CDate(strDVDate2) & ""
[/VB]

I might ben doing this totally wrong (wouldnt be the first time) :D
 
You should use >= and <=

More/Less before equal is the correct syntax
But Id use between!

HTH
/Kejpa
 
kejpa,

tried youre suggestion and came up with error:
"An unhandled exception of type System.InvalidCastException occurred in microsoft.visualbasic.dll

Additional information: Cast from string "29/08/2004" to type Long is not valid."

Here is my adjusted code:

[VB]
strNewSQLStatement = "SELECT * From IssueHistory WHERE " & _
strSelectedSearchTypeDate1 & " between " & (strDVDate1 And strDVDate2) & " and " & _
strSelectedSearchTypeDate2 & " between " & (strDVDate1 And strDVDate2)
[/VB]

strDVDate1 = 29/08/2004
strDVDate2 = 29/08/2005

strSelectedSearchTypeDate1 = SignOutDate
strSelectedSearchTypeDate2 = SignInDate
 
Sorry, missed a few string handling issues....

Here is my adjusted code:

[VB]
strNewSQLStatement = "SELECT * From IssueHistory WHERE (" & _
strSelectedSearchTypeDate1 & " between " & strDVDate1 & " And " & strDVDate2 & ") and (" & _
strSelectedSearchTypeDate2 & " between " & strDVDate1 & " And " & strDVDate2 & ")"
[/VB]

But in order to avoid injection youd rather use parameters...

HTH
/Kejpa
 
Small update to kejpas example.
You can check the documentation, but SQL Server will usually want dates in a specific format. Youre using Day/Month/Year but SQL will usually want Month/Day/Year (USA format).

Are you converting a real DateTime value into a string and putting that in strDVDate1 and strDVDate2? If so, Id do the ToString on the dates:
Code: 
 Assumes you have two existing DateTime variables that you want to use.
 I called them Date1 and Date2
Dim Date1 As DateTime
Dim Date2 As DateTime
Dim strDVDate1 As String
Dim strDVDate2 As String

strDVDate1 = Date1.ToString("MM/dd/yyyy")
strDVDate2 = Date2.ToString("MM/dd/yyyy")
strNewSQLStatement = "SELECT * From IssueHistory WHERE (" & _
                            strDVDate1 & " between " & strDVDate1  & " And " & strDVDate2 & ") and (" & _ 
                            strDVDate1 & " between " & strDVDate1  & " And " & strDVDate2 & ")"

If you just have two strings, youll have to manually parse them into the correct format or let the DateTime type do it for you. The following code doesnt do any error handling to assure that strDVDate1 and strDVDate2 are valid dates - but it should.
Code: 
 Assume you strDVDate1 and strDVDate2 defined
Dim Date1 As DateTime
Dim Date2 As DateTime

 Clone the current DateTimeFormat so we can make changes
 Not sure how to use DirectCast - Im C#
 Im trying to cast Clone(), which returns object, into DateTimeFormatInfo
 Hopefully Im using DirectCast right
DateTimeFormatInfo dateTimeInfo = DirectCast(DateTimeFormatInfo.CurrentInfo.Clone(), DateTimeFormatInfo)

 Change the ShortDatePattern, which controls the format
 that DateTime.Parse looks at
dateTimeInfo.ShortDatePattern = "dd/MM/yyyy"
Date1 = DateTime.Parse(strDVDate1, dateTimeInfo);
Date2 = DateTime.Parse(strDVDate2, dateTimeInfo);

 Now you can use the code in the first example to use Date1 and Date2 with their ToString methods.

With either of these methods, you dont have to worry about SQL injection. SQL injection cant occur above because the only dynamic portion of the string is the Date, which youre controlling using DateTime types. If you were to use strings directly, then youd have to worry about SQL injection and double up your single quotes. If you need help there, just search the forums or ask away.

-ner
 
Hi Guys,

Have tried both examples, and the follow error occurs, on the following line of code where strTableName is the name of the Table in this case IssueHistory.
[VB]
dim ds as New DataSet

daDataAdapter.Fill(ds, strTableName)
[/VB]
"Data type mismatch in criteria expression".


@ Nerseus - I was unable to get the DateTimeFormatInfo part working but I tried to the date1.tostring and .toshortdatestring. with the same error as above.

Im connecting to an Access Db does that make a difference???? The Columns SignInDate and SgnOutDate are both set to short date and formatted to dd/MM/yyyy.
 
Last edited by a moderator:
SonicBoomAu said:
Im connecting to an Access Db does that make a difference???? The Columns SignInDate and SgnOutDate are both set to short date and formatted to dd/MM/yyyy.
Click to expand...

Thats it!
Access only accepts the US standard date format... Even if you have a localized version (swedish in my experience) the only way you can insert dates is with the US date format MM/dd/yyyy. Simply wonderful.

HTH
/Kejpa
 
I dont know enough about Access to know if you can modify the date format and let SQL queries work with that specified format.

I think that you must surround your date values with the pound sign (#) for it to work. My original sample modified:
Code: 
 Assumes you have two existing DateTime variables that you want to use.
 I called them Date1 and Date2
Dim Date1 As DateTime
Dim Date2 As DateTime
Dim strDVDate1 As String
Dim strDVDate2 As String

strDVDate1 = Date1.ToString("MM/dd/yyyy")
strDVDate2 = Date2.ToString("MM/dd/yyyy")
strNewSQLStatement = "SELECT * From IssueHistory WHERE (" & _
                            strSelectedSearchTypeDate1 & " between #" & strDVDate1 & "# And #" & strDVDate2 & "#) and (" & _ 
                            strSelectedSearchTypeDate2 & " between #" & strDVDate1 & "# And #" & strDVDate2 & "#)"

I also had used strDVDate1 far too many times :)

If this doesnt work, show us what your SQL string is (strNewSQLStatement above) so we can help solve the problem. Also, try pasting the query youre building directly into Accesss query builder to see if it works.

If all else fails, zip up the DB and let us take a look.

-ner
 
SonicBoomAu said:
If that was the case how come I can set see the dates in the following format dd/MM/yyyy in the DB?
Click to expand...
When entering data into the tables directly its no problem using the native way of entering data, its when you try to do it using SQL you have to use the US way. Dont ask me why.
As Ner pointed out (and I forgot...) you need to have # around your dates.

The easiest way to test if your SQL is working is by setting a breakpoint when the SQL is generated, copy it and paste it into a query window. It will help you out in making the SQL have the right dialect.

HTH
/Kejpa

btw SQL = Standard Query Language
Standards are great, everyone should have his....
 
And it gets alot easier if you use the Cmd.Parameters.
Than the OleDB or whichone ever does the translation.
 
Hey Guys,

Thanks for your help. As soon I as placed the # sign in it all worked. :D

Just to satisfy my own mind I tried with the date being formatted to "MM/dd/yyyy" and "dd/MM/yyyy". And it worked for both date formats.

Once again thank you for all your help.
 
SonicBoomAu said:
Just to satisfy my own mind I tried with the date being formatted to "MM/dd/yyyy" and "dd/MM/yyyy". And it worked for both date formats.
Click to expand...
Both worked?!?
What happens if you put in 1/9/2005? Does it give you Sept 1 or Jan 9?!?

/Kejpa
 
I dont think it really matters what format I put the date in. I originally get the date from a DateTimePicker which is formatted to dd/MM/yyyy.

I ran the function with MM first and got 8 records then I ran it with dd and receive the exact same records.
 
Back
Top