sql statement select range 2 date field in sql server

[cyberizal]

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.

[alex_read]

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...

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

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

[Memnoch1207]

This is how I do mine

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

[nokia8210]

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.