CODEWITHSUNDEEP
c#sqlstringdate
Drabe
Drabe

Reputation: 45

Finding records between a given date range in sql with a varchar column

I have the following problem. I wrote a query to get the date of an order in c#:

QueryDate = string.Format("(Order.Begin>= '{0}' and Order.End<= '{1}')", BeginDate, EndDate);

The problem is my SQL Server table columns, 'Begin' and 'End', are varchar columns and I want to keep them this way. They are saved as '12-4-2012' for example.

Now I want to get the dates between say '12-4-2012' and '19-4-2012'. But the result is everything between '12' and '19' with the first 2 digits. So the results also include '14-8-2011' for example.

Is there a way retrieve only the dates between '12-4-2012' and '19-4-2012' with a string column?

Upvotes: 2

Views: 1965

Answers (3)

Steve
Steve

Reputation: 216312

Use Parameters and encapsulate you column names with square brackets
Then supposing your input strings are valid date you could do:

DateTime dStart = DateTime.ParseExact(BeginDate, "dd/MM/yyyy", CurrentCulture.CultureInfo); 
DateTime dEnd = DateTime.ParseExact(EndDate, "dd/MM/yyyy", CurrentCulture.CultureInfo); 

string QueryDate = "SELECT * FROM Order " + 
                   "WHERE CONVERT('smalldatetime', [Begin], 105) >= @start " + 
                   "AND CONVERT('smalldatetime', [End], 105) <= @end";
SqlCommand cmd = new SqlCommand(QueryDate, con);
cmd.Parameters.AddWithValue("@start", dStart);
cmd.Parameters.AddWithValue("@end", dEnd);
SqlDataReader reader = cmd.ExecuteReader();

Upvotes: 1

MajorInc
MajorInc

Reputation: 354

I think you can convert the Begin and Order columns into DateTime format something like this:

QueryDate = string.Format("(convert(datetime, Order.Begin, 105) >= @startdate and convert(datetime, Order.End, 105)<= @enddate;"

And then add he @startdate, and @enddae parameters...

Upvotes: 1

Francisco Spaeth
Francisco Spaeth

Reputation: 23913

Wouldn't this be a possibility?

string.Format("(convert(datetime, Order.Begin, 105)>= convert(datetime, '{0}', 105) and convert(datetime, Order.End, 105)<= convert(datetime, '{1}', 105))", BeginDate, EndDate);

Upvotes: 0

Related Questions