How to convert varchar value in date

Question

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.

SQL

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.

Posted 3-Mar-22 22:14pm

him_mca

Updated 4-Mar-22 9:01am

Sean Ewington

v4

Add a Solution

Comments

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.

2 solutions

Solution 1

Try this

SQL

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

INSERT INTO @Tbl_DailySalesReport_Manual
SELECT '30/06/2021'
UNION ALL
SELECT '01/07/2021'
UNION ALL
SELECT '02/07/2021'
UNION ALL
SELECT '03/07/2021'
UNION ALL
SELECT '04/07/2021'
UNION ALL
SELECT '10/07/2021'
UNION ALL
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)

Posted 3-Mar-22 22:38pm

_Asif_

Comments

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2022-03-03T23:54:00

Solution 2

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.

Posted 3-Mar-22 23:54pm

OriginalGriff

Comments

_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.

P.S:
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.