CODEWITHSUNDEEP
c#sqlvisual-studio-2010
waleed-gfx
waleed-gfx

Reputation: 23

i want to select data when date between dd/mm/yyyy and dd/mm/yyyy

I want to select data where date between dd/mm/yyyy and dd/mm/yyyy

If the date in the same month I find data

For instance

SELECT * 
FROM Table Name 
WHERE date between '21/07/2014' and '29/07/2014'

If the date between two different Months .. the result is Null

For instance

SELECT * 
FROM Table Name 
WHERE date between '21/07/2014' and '1/08/2014'

Upvotes: 1

Views: 2603

Answers (5)

Manish kumar
Manish kumar

Reputation: 11

You can also write you'r query like this

SELECT * FROM Table Name WHERE date between '21-Aug-2014' and '1-Sep-2014'

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

If you absolutely want to use DD/MM/YY format that is style 103 if you convert, ie.

select *
from tbl
where dt between convert(date,'21/07/2014',103) and convert(date,'01/08/2014',103)

See fiddle at: http://sqlfiddle.com/#!6/f199c/6/0

http://www.w3schools.com/sql/func_convert.asp

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062855

Dates don't have formats. Since you cite C#, it seems that this code is coming from a .NET caller - in which case, parameterize:

DateTime from = ..., to = ... // perhaps DateTime.Parse or DateTime.ParseExact,
                              // but perhaps DateTime.Now.Date.Add(...)
cmd.CommandText = @"
SELECT * 
FROM Table Name 
WHERE date between @from and @to";
cmd.Parameters.AddWithValue("from", from);
cmd.Parameters.AddWithValue("to", to);

Now there is no ambiguity whatsoever.

Upvotes: 1

Wiktor Zychla
Wiktor Zychla

Reputation: 48240

Use the ISO 8601 universal format. e.g. '2007-04-05T14:30'. It is interpreted regardless of locale settings.

http://en.wikipedia.org/wiki/ISO_8601#Dates

Upvotes: 0

DavidG
DavidG

Reputation: 118977

When using dates in SQL Server it pays to be explicit. In your second example it's likely SQL Server is converting the second date to 8th January 2014 as it will assume US style formatting like mm/dd/yyyy. I always write my date in yyyy-mm-dd format but it's probably better to use ISO8601 format as YYYYMMDD which (as marc_s points out) is a much more universal format:

SELECT * 
FROM TableName 
WHERE date between '20140721' and '20140801'

Upvotes: 2

Related Questions