Date formating

1

hi
I have a date in a table as below
6/30/2021 5:09:04 PM
i am trying to filter the dates as
select * from x where dt between '01-apr-2021' and '30-jun-2021'
i should be retrieving say 1857 rows but getting only 1815 rows
but when
i tried as below
select * from x where format(dt,'dd-MMM-yyyy') between '01-apr-2021' and '30-jun-2021'
filter is ignored ?
what wrong I am doing here?

2 
SELECT *
FROM X
WHERE dt >= '20210401'
	AND dt < '20210701';
3

sir
same result 1815 rows only
i have inserted data from csv file
when I open csv file and apply filter I get 1857 records

4

Did all the records get inserted from CSV?
check the total counts from CSV and table.

5

yes sir
all records are inserted
i have confirmed that

6

why would sql ignores filtered values when I
write query as below
select * from x where format(dt,'dd-MMM-yyyy') between '01-apr-2021' and '30-jun-2021'

7

What is the datatype for dt column in the table?

8

datetime2

9

can you try any other format and see the results.
Eg: DD/MM/YYYY

10

issue resolved sir

i used convert(datetime,format(dt,'dd-MMM-yyyy') ,103) and then applied the filter it worked

thanks for your time

1 Like