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

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