CODEWITHSUNDEEP
sqlsql-server
user3262364
user3262364

Reputation: 371

cast date with nvarchar not working in sql query

I have a query like this :

SELECT DISTINCT
       [Date_of_Receipt]
FROM RPT_VIEW_Receive
WHERE CAST([Date_of_Receipt] AS NVARCHAR) >= '12-12-2016'
ORDER BY [Date_of_Receipt];
SELECT DISTINCT
       [Date_of_Receipt]
FROM RPT_VIEW_Receive;

enter image description here

i have a view with name RPT_VIEW_Receive

in my view all date is greater than 2017 ,

but in my first query i want to show all records greater than 2016..but its not showing all records ,

what could be the issue???

Upvotes: 0

Views: 657

Answers (4)

Pawan Kumar
Pawan Kumar

Reputation: 2021

Please try like this..Do not use casting in the where clause. It will hamper the performance. What is the data type of Date_of_Receipt ?

select distinct  [Date_of_Receipt] from RPT_VIEW_Receive  
where [Date_of_Receipt] >= '20170101'
order by [Date_of_Receipt]

Upvotes: 0

DxTx
DxTx

Reputation: 3357

Try this query If you have stored dates as nvarchar(50).

SELECT DISTINCT [Date_of_Receipt]
FROM Table
WHERE Cast([Date_of_Receipt] AS DATE) >= '12/12/2016'
ORDER BY [Date_of_Receipt];

If dates are stored as date (which is recommended), you can simply do this.

SELECT DISTINCT [Date_of_Receipt]
FROM Table
WHERE [Date_of_Receipt] >= '12/12/2016'
ORDER BY [Date_of_Receipt];

Note: This will show all records greater than 11/12/2016. If you want to show all record greater than 2016, use 01/01/2017 instead of 12/12/2016.

Upvotes: 0

ChandraKanth Narumanchi
ChandraKanth Narumanchi

Reputation: 61

Your problem seems to be with the format of the date column [Date_of_Receipt]. We have to note that the date is in DD/MM/YYYY.

Try using CONVERT instead of CAST. 

SELECT DISTINCT
       CONVERT(DATE,[Date_of_Receipt],103)
FROM RPT_VIEW_Receive

The last parameter for CONVERT is the format we are using for parsing. That should work.

Upvotes: 1

Dipak Delvadiya
Dipak Delvadiya

Reputation: 2112

try this one.. Check the static date in second operand of where that you passed(12-12-2016)

SELECT DISTINCT
       [Date_of_Receipt]
FROM RPT_VIEW_Receive
WHERE CAST([Date_of_Receipt] AS date) >=  CAST('12-12-2016' AS date)
ORDER BY [Date_of_Receipt];
SELECT DISTINCT
       [Date_of_Receipt]
FROM RPT_VIEW_Receive;

Upvotes: 1

Related Questions