Problem with SQL-Server Date Query

der flori

hi, i have a little problem with the following Query:

string str_Verlaufdate = "'" + p.Verlauf_date.Month + "/" + p.Verlauf_date.Day + "/" + p.Verlauf_date.Year + "'";

DataRow[] dr2 = this.dataNet.Tables["Verlauf"].Select("BehID = '"+v[0].ToString()+"' and Date = "+str_Verlaufdate);

I don´t get any result, but there exist a lot of records that match to this date in the database!

Dino Mulahusic

How are your dates stored? If they are stored with time (3/3/2009 11:12:13) then you wont get any results. try entering your query in sql server and see what you get you can also try something like this

string str_Verlaufdate = "'" + p.Verlauf_date.Month + "/" + p.Verlauf_date.Day + "/" + p.Verlauf_date.Year + "%'";

DataRow[] dr2 = this.dataNet.Tables["Verlauf"].Select("BehID = '"+v[0].ToString()+"' and Date LIKE "+str_Verlaufdate);

der flori

yes, they are stored with time! But when I change the string in this way I get an syntax error:

string str_Verlaufdate = "'" + p.Verlauf_date.Month + "/" + p.Verlauf_date.Day + "/" + p.Verlauf_date.Year + " 00:00:00'";

I don´t know, how the format of the string has to look like.

Searril

When I have to search by dates with times I do a 'from date' and a 'to date' because of the time issues. The from date should be the date with a time of midnight and end date would have a time of 1 second before midnight. For example, if I wanted everything from April 1 through April 7 I would search for: 2009/04/01 00:00:00 through 2009/04/07 23:59:59

Dino Mulahusic

look at your table data and see what format it is and try something like this

string str_Verlaufdate = "'" + p.Verlauf_date.ToString("mm/dd/yyyy HH:mm:ss") + "'";

or some other format do some research here[^]

der flori

thanks a lot, this link solved my problem :)

Dave Kreskowiak

Now, forget everything you just learned and do it the correct way using parameterized queries[^] instead. If you did that first, this would never have been a problem.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008

PIEBALDconsult

Hear hear! There's no substitute for doing it the right way.