CODEWITHSUNDEEP
sql
user1342164
user1342164

Reputation: 1454

SQL Query not returning data between date range

I am trying to return results between column "Date of Payment" and when I run the query below I am not getting the correct results. I get data from that column where it could be 10/1/2013..Any idea what I might be doing wrong here? I just want the rows for 2017.. TIA

Date of payment column type is a nvarchar(50)

Sample column data is like 10/31/2017...

SELECT     ID,  [Date of Payment]
FROM         tblData
WHERE     ([Date of Payment] BETWEEN '1/1/2017' AND '12/31/2017')

Upvotes: 0

Views: 1993

Answers (2)

user311086
user311086

Reputation: 1088

Try next code And avoid put spaces in field name and replace it with underscore 

SELECT     ID,  PaymentDate
FROM         tblData
WHERE (PaymentDate BETWEEN CONVERT(DATETIME, '2017-01-01', 102) AND CONVERT(DATETIME, '2017-12-30', 102))

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

(I am assuming SQL Server based on the syntax.)

Fix the [Date of Payment] data in your table.

update tblData
    set [Date of Payment] = convert(date, [Date of Payment], 101);

alter tblData
    alter column [Date of Payment] date;

Voila! Your problem is fixed!

You should also change the data type to use ISO standard formats:

WHERE [Date of Payment] BETWEEN '2017-01-01' AND '2017-12-31'

Upvotes: 0

Related Questions