sql statement select range 2 date field in sql server

1. how do i write a sql statement to select 2 range date fields in sql server
i tried in usual vb6 code but its not working

vb.net & sql server
=============
field name = datein, dateout
so it will be datein => txtdatein.text and dateout =< txtdateout.text

my date format is "dd/MM/yyyy"


2. if i use this below method, the sql server reads the date format as "MM/dd/yyyy" but in my database date stored as "dd/MM/yyyy". how do i get the format to read field in db as "dd/MM/yyyy".

Where DateIn >= '" & txtDateIn.Text & "' and DateOut <= '" & txtdateOut.Text & "' "

thanks guys.

you can use the between method for 2 dates like that. From an SQL statment, I think you're forced to use the really wierd SQL Server format, however if you can use stored procedures, you can do that on by calling the DATEFORMAT method...

Code:

---

CREATE  PROCEDURE spMyStoredProcName
@DateIn (fieldtype),
@DateOut (fieldtype)
AS
SET DATEFORMAT dmy

SELECT * FROM mytable
WHERE datein BETWEEN @DateIn AND @DateOut

---

This is how I do mine

VB Code:

CREATE PROCEDURE stp_GetMailingByDateRange
    @StartDate varchar(10),
    @EndDate varchar(10)
AS
SELECT A.ESPEmail_MailingID, 
    A.SubjectLine,
    A.MailingDesc,
    A.EmailCreator
FROM dbo.ESPEmail_Mailing As A
[color=red]WHERE A.MailingDate >= @StartDate AND A.MailingDate <= @EndDate[/color]
AND A.InActive = 0
AND A.EmailSent = 0
ORDER BY A.SubjectLine, A.MailingDesc

The Date format needs to be : 'DD/MMM/yy'

this then tells the sql server which part is the month
then just convert it back when you get the info


by Format(Date,"dd/MM/yy")

you also need to dim dates as show below

so it should look like this:

Dim DateIn as Date
Dim DateOut as Date

DateIn = Format(txtBox.Text,"dd/MMM/yy")
DateOut = Format(txtBox2.Text,"dd/MMM/yy")

then put the DateIn and DateOut into the query this should slove it.