get sql date between

Question

I want to get data from a range of selected dates in SQL Server.

This is the SQL query (SQL Server 2005)

SELECT  * FROM  slips WHERE  CONVERT(char,  date_Of_Birth, 101)  BETWEEN ('01/08/2019') AND ('21/08/2019')

It gives me a results of every record on my database, how do I get selected range? I want only the selected range to show.

SQL questions not declaring the database system in use are like language questions hiding the language in question. Mostly broken. (Looks like you mean MS SQL Server.) — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 3, 2019 at 10:43
You've learnt an unhelpful rule (don't worry about it, everyone does at some point) that you can make things work if you just treat everything as strings. Unfortunately, that rule only really exists if you define "work" as "compiles and runs without raising language errors". It rarely works if you want correct results. The solution is to work in the other direction - get all of your data, variables, etc to use the correct data types. That way things like comparisons can use that type's rules, rather than the string rules. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Sep 3, 2019 at 10:48
@fhulufhelohanyahanya What is the datatype of column date_Of_Birth — Zulqarnain Jalil
– Zulqarnain Jalil, Commented Sep 3, 2019 at 11:29
@ZulqarnainJalil im using nvarchar. i changed my date format from M/d/yyyy to dd/MM/yyyy and its no longer getting selected range — fhulufhelo hanyahanya
– fhulufhelo hanyahanya, Commented Sep 3, 2019 at 11:52
@fhulufhelohanyahanya then you should the second query i have posted in my answer — Zulqarnain Jalil
– Zulqarnain Jalil, Commented Sep 3, 2019 at 11:58

Gordon Linoff · Accepted Answer · 2019-09-03 10:51:17Z

3

Do not use between with dates. Here is a good explanation of why not.

Also, do not use string comparisons for dates. SQL Server has lots of nice built in date functionality.

I would recommend:

select s.*
from slips s
where s.date_of_birth >= '2019-08-01' and
      s.date_of_birth < '2019-08-22'

answered Sep 3, 2019 at 10:51

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zulqarnain Jalil · Accepted Answer · 2019-09-03 11:35:59Z

1

I assume that your date_of_birth is date type then you need to convert your string to date

SELECT  * FROM  slips WHERE  date_Of_Birth  BETWEEN CONVERT(date,  '01/08/2019', 103) AND CONVERT(date,  '21/08/2019', 103)

if your date_of_birth is string type the you shoud use this

SELECT  * FROM  slips WHERE  CONVERT(date,  date_Of_Birth,103)  BETWEEN CONVERT(date,  '01/08/2019', 103) AND CONVERT(date,  '21/08/2019', 103)

answered Sep 3, 2019 at 11:35

Zulqarnain Jalil

1,69117 silver badges27 bronze badges

Roelant · Accepted Answer · 2019-09-03 10:17:35Z

0

You can use BETWEEN with dates (see also)

SELECT
    * 
FROM slips 
WHERE date_Of_Birth BETWEEN '01/08/2019' AND '21/08/2019'

answered Sep 3, 2019 at 10:17

Roelant

5,1995 gold badges43 silver badges77 bronze badges

karthik kasubha · Accepted Answer · 2019-09-03 11:00:41Z

If you want to select the slips in last N months - Replace N with Months

Select *
FROM slips 
Where DATEDIFF(MONTH, date_of_birth, GETDATE()) Between 1 and N

If you want to select the slips in last N days - Replace N with days

Select *
FROM slips 
Where DATEDIFF(DAY, date_of_birth, GETDATE()) Between 1 and N

If you want to select the slips in last N years - - Replace N with Year

Select *
FROM slips 
Where DATEDIFF(YEAR, date_of_birth, GETDATE()) Between 1 and N