SQL Querie Question

[SonicBoomAu]

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.

 

strNewSQLStatement = "SELECT * From IssueHistory WHERE " & _
                           SignOutDate <= strDVDate1 And _
                           SignInDate >= strDVDate2

[Nerseus]

I didn't 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

[kejpa]

strNewSQLStatement = "SELECT * From IssueHistory WHERE " & _
                           (SignOutDate between strDVDate1 And strDVDate2) and 
                           (SignInDate between strDVDate1 And strDVDate2) 

 

Most SQL dialects accepts "between" AFAIK

 

HTH

/Kejpa

[SonicBoomAu]

Hi Nerseus,

 

Need help with SQL Statement.

I'm getting a "Syntax error (missing operator) in query expression 'SignOutDate => '10/12/2004' And SignOutDate =< '10/12/2005".

 

I'm just not sure about the syntax with the where part of the statement.

 

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

 

I might ben doing this totally wrong (wouldn't be the first time) :D

[kejpa]

You should use >= and <=

 

More/Less before equal is the correct syntax

But I'd use between!

 

HTH

/Kejpa

[SonicBoomAu]

kejpa,

 

tried you're 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:

 

strNewSQLStatement = "SELECT * From IssueHistory WHERE " & _
                           strSelectedSearchTypeDate1 & " between '" & (strDVDate1 And strDVDate2) & "' and " & _
                           strSelectedSearchTypeDate2 & " between '" & (strDVDate1 And strDVDate2)

 

strDVDate1 = 29/08/2004

strDVDate2 = 29/08/2005

 

strSelectedSearchTypeDate1 = SignOutDate

strSelectedSearchTypeDate2 = SignInDate

[SonicBoomAu]

Sorry , my Internet is really slow.

[kejpa]

Sorry, missed a few string handling issues....

 

Here is my adjusted code:

 

strNewSQLStatement = "SELECT * From IssueHistory WHERE (" & _
                           strSelectedSearchTypeDate1 & " between '" & strDVDate1  & "' And '" & strDVDate2 & "') and (" & _ 
                           strSelectedSearchTypeDate2 & " between '" & strDVDate1  & "' And '" & strDVDate2 & "')"

 

But in order to avoid injection you'd rather use parameters...

 

HTH

/Kejpa

[Nerseus]

Small update to kejpa's example.

You can check the documentation, but SQL Server will usually want dates in a specific format. You're 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, I'd do the ToString on the dates:

' 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, you'll have to manually parse them into the correct format or let the DateTime type do it for you. The following code doesn't do any error handling to assure that strDVDate1 and strDVDate2 are valid dates - but it should.

' 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 - I'm C#
' I'm trying to cast Clone(), which returns object, into DateTimeFormatInfo
' Hopefully I'm 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 don't have to worry about SQL injection. SQL injection can't occur above because the only dynamic portion of the string is the Date, which you're controlling using DateTime types. If you were to use strings directly, then you'd 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

[SonicBoomAu]

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.

dim ds as New DataSet

daDataAdapter.Fill(ds, strTableName)

"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.

 

I'm 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.

Edited August 30, 2005 by SonicBoomAu

[kejpa]

I'm 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.

 

That's 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

[SonicBoomAu]

If that was the case how come I can set see the dates in the following format dd/MM/yyyy in the DB?

[Nerseus]

I don't 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:

' 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 doesn't work, show us what your SQL string is (strNewSQLStatement above) so we can help solve the problem. Also, try pasting the query you're building directly into Access's query builder to see if it works.

 

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

 

-ner

[kejpa]

If that was the case how come I can set see the dates in the following format dd/MM/yyyy in the DB?

When entering data into the tables directly it's no problem using the native way of entering data, it's when you try to do it using SQL you have to use the US way. Don't 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....

[FZelle]

And it gets alot easier if you use the Cmd.Parameters.

Than the OleDB or whichone ever does the translation.

[SonicBoomAu]

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.

[kejpa]

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.

Both worked?!?

What happens if you put in 1/9/2005? Does it give you Sept 1 or Jan 9?!?

 

/Kejpa

[SonicBoomAu]

I don't 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.

[SonicBoomAu]

Sorry just re-check my code. You are correct the date must be formatted to MM/dd/yyyy not dd/MM/yyyy.