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, Sep 03 '19 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, Sep 03 '19 at 10:48
@fhulufhelohanyahanya What is the datatype of column **date_Of_Birth** — Zulqarnain Jalil, Sep 03 '19 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, Sep 03 '19 at 11:52
@fhulufhelohanyahanya then you should the second query i have posted in my answer — Zulqarnain Jalil, Sep 03 '19 at 11:58

score 3 · Accepted Answer · answered Sep 03 '19 at 10:51

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'

score 1 · Answer 2 · answered Sep 03 '19 at 11:35

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)

score 0 · Answer 3 · answered Sep 03 '19 at 10:17

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 03 '19 at 10:17

Roelant

score 0 · Answer 4 · answered Sep 03 '19 at 11:00

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

4 Answers4