Click here to Skip to main content
15,846,899 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Team,

I have a situation that the date is getting updated in the table column
[Cart Generation Date] which is defined as nvarchar(255)

Now if I select a query to fetch a record with the date range it is not pulling correct data.

select * from Tbl_DailySalesReport_Manual
where [Cart Generation Date] between '01/07/2021' and '10/07/2021'

Please advise.

What I have tried:

Tried to cast date, but I have not succeeded.
Updated 4-Mar-22 10:01am
Richard MacCutchan 4-Mar-22 4:24am    
Your design is wrong. Dates should always be stored as SQL Date or DateTime types.
Maciej Los 4-Mar-22 14:38pm    
Share your sample data, where conversion is failed.

The best - and pretty much only - real solution is to change your DB design, so that you store all values in appropriate data types: numeric values in INT FLOAT or DECIMAL fields, dates in DATETIME, DATE, or DATETIME2 columns.

WHile it's possible to get round the problem temporarily with CONVERT operations, if the current design gets into production as is, it'll cause you some major headaches: different users use different date formats: mm/dd/yy, dd/mm/yy, and yy/mm/dd being the most common - and a date like 01/02/03 stored as NVARCHAR data can't be reliably converted to a date because there are at least three different meanings it could have had: 1st Feb 2003, 2nd Jan 2003, 3rd Feb 2001. When you store dates in strings, you lose all the user context, and thus any ability to trust the dates you get later. Additionally, it's quite possible to get dates like "I dunno" or "today" in your DB and they will always fail CONVERT operations.

Using the right datatypes gets away from that: dates are checked and converted at the time of user entry, using user preferences, and passed to SQL as DateTime objects for storage in a date-based field. No conversion is ever needed, and date will always be valid in your DB.
Share this answer
_Asif_ 4-Mar-22 8:12am    
My vote of 1:
In significant number of cases, we as developer when join a company usually work in an environment where application is already in Production, used by thousands of users. In these legacy systems, developer usually inherits database design issues present in Production and for a number of reasons, correcting these design issues
requires deep product understanding, and most importantly impact analysis is required if there are third party APIs doing operations over these data.

Apart from that there are legit cases where dates do come as string and stored as string. For example in financial domain dates comes from host/middleware as string and stored as string in Audit Tables as per standard message format.
Maciej Los 4-Mar-22 14:42pm    
Sorry, but i have to disagree. OP did not explain, if he's working on "home-project" or "production-project". At this moment @OriginalGriff is right.
CHill60 7-Mar-22 6:28am    
I work in the financial domain and the only time dates are passed as strings is in certain message types e.g. Swift, JSON, XML. However, there is still no excuse for not persistently storing dates as dates.
There are many ways we can determine that this table was not designed by a professional - spaces in column names for a start and prefixing a table with "tbl_"
Downvote countered.
_Asif_ 7-Mar-22 7:51am    
Below is the actual message (hiding customer details), the channel received from Middleware.
000187UNISON2|20|xxUserid|0210|20210605120248|06|CRM|4200000000001|115|20210605|0000001|000013|00000000000|00|1|Customer Name|00000000000001|0047|XXXXXXX|PKR||000000000000001|A|30|20|1|

As you can see the 5th Element is transaction date time. This received message goes through multiple stages, but the very first step the channel does
is to store this message in a table for Audit / Compliance purpose so that in case of investigation the channel precisely know what it has received from
Middleware. On later stages, we do parse the message and store it as per their data types.

There is another aspect that we should not understimate, and that is performance. Our Middleware processes 5K Transactions per second, and for that speed everything matters.

From the Question Perspective, what information are we getting.
1. This is a shopping cart kind of application
2. This application is already in production because OP got a "situation" where date is getting updated and that daily sales report is being generated for quite some time
3. The table name suggests it is some intermidiate table generated by backend process (Probably some DB Job) for daily reporting purpose
4. The report in question is not an automated report
5. This shopping cart application is developed by a novice software engineer.
6. It seems that a new requirement in terms of filter have been requested by Business team and this developer (OP) is working on this report filter.

From all above undestanding (for myself only) i have resolved his issue by providing him a solution for his immidiate problem.

You all are free to downvote as many time as you want :) Thats not my concern. I am here to learn and to share the knowledge I have.
Try this

DECLARE @Tbl_DailySalesReport_Manual TABLE
	[Cart Generation Date] NVARCHAR(255)

INSERT INTO @Tbl_DailySalesReport_Manual
SELECT '30/06/2021'
SELECT '01/07/2021'
SELECT '02/07/2021'
SELECT '03/07/2021'
SELECT '04/07/2021'
SELECT '10/07/2021'
SELECT '11/07/2021'

select *
from	@Tbl_DailySalesReport_Manual
where convert(datetime, [Cart Generation Date],103) between convert(datetime, '01/07/2021', 103) and convert(datetime, '10/07/2021', 103)
Share this answer
Maciej Los 4-Mar-22 14:45pm    
I won't judge your answer, Asif. If OP will confirm that he's working in production environment, i'll upvote it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900