compare dates

[tariqi]

am using sql 2005 and vb 2008.i need to compare dates and select matching records betweem a selected date range.say records between 11/13/2010 and 11/20/2010.e.thing is working ok except when it comes to selecting dates between 11/1/2010 and 11/9/2010.this is happening because when am retrieving the dates from database,its being retrieved in the format 11/01/2010 instead of 11/1/2010 and so it reports no records found,and knowing the limits of sql 2005 this is the only way i can retrieve the date.

anyone who knows how to go about this?

below is the code..

myCmd3 = New SqlCommand("Select sequenceID, CONVERT (varchar(10), CallDate, 101) AS CallDate from........

Dim date1 As DateTime = Convert.ToDateTime(TextBoxStartDate.Text)
Dim date2 As DateTime = Convert.ToDateTime(TextBoxEndDate.Text)......


condition += CallDate >=  '" + date1 + "' and CallDate <= '" + date2 + "' "........

[alc6379]

instead of using date1 or date2, use String.Format("MM/dd/yyyy", date1) in your SQL statement. That should format it how you need.

[tariqi]

thanks,but that didnt work, the format function just returns the string "mm/dd/yyyy" and this string is assigned to variable date1.
any other ideas? your assistance is appreciated

[tariqi]

anyone who can help me with this problem please?

[Netcode]

why not make your search in the same format as in sql server
in order to avoid errors

[codeorder]

See if this helps.

Dim startDate As Date = "10/09/10" '// preset Start Date.
        Dim endDate As Date = "10/25/10" '// preset End Date.
        Dim tempDate As Date = "10/23/10" '// Date that is currently being searched and compared to Start/End Dates.
        If tempDate > startDate And tempDate < endDate Then '// check if greater than startDate and lower than endDate.
            MsgBox("Date matches the preset time span.", MsgBoxStyle.Information)
        Else
            MsgBox("Incorrect Date located.", MsgBoxStyle.Critical)
        End If

[Unhnd_Exception]

Try looking up the datepart function in sqlserverce.

[tariqi]

i found a solution ,below:

select right(rtrim(day(Calldate)),2) + '/' + right(rtrim(month(CallDate)),2) + '/' + rtrim(year(CallDate))

added this to sql statement