date comparision in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

i have a table which contains date field .the datatype of the field is varchar.I need to compare these dates in one query also in another query i need to calculate previous date from current date also.how i should write this query?it gives me conversion error.

my query is as follows without converting any date fields to datetime format of sql server.

SQL

select SuppliedDate,VrNo,OrderNo, (First_name + ' ' + Last_name) as Name,DoB,Gender,ShippingType,Address,City,State,Country,ZipCode,DrName,PName,vr_date,Status,Remark
from TransactionMaster where
((SuppliedDate=null and (vr_date >=  CONVERT(CHAR(12), DATEADD(dd, -1, '11/30/2013'), 112) AND vr_date <= '11/30/2013')) and AdminEdit='0')

It doesnt return me any rows.
if i convert fields to datetime format it gives me conversion error.

Posted 16-Dec-13 22:40pm

pwavell

Updated 16-Dec-13 23:14pm

v2

Add a Solution

Comments

An@nd Rajan10 17-Dec-13 4:45am

please write your code and mention which line have error,that is helpful for get currct solution

2 solutions

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)

Maciej Los · Accepted Answer · 2013-12-16T22:43:00

Solution 1

There are several reasons to not store datetime value as varchar.

Try to use CONVERT[^] together with Date And Time[^] functions.

Posted 16-Dec-13 22:43pm

Maciej Los

Comments

thatraja 17-Dec-13 4:56am

5!

Maciej Los 17-Dec-13 4:58am

Thank you ;)

OriginalGriff · Accepted Answer · 2013-12-16T22:47:00

Yes, it will.

Do not store dates as strings: that is what the DateTime datatype is there for.

You can't compare dates stored as strings without converting them to DateTime first: if you try you will get a string comparison, which compares character by character. And converting strings to dates on the fly normally just exposes that your data is not consistent or in the right format - but too late to do anything useful about it.

Always convert user entered date strings to DateTime values as early as possible, and work with the DateTime value from then on, converting back to a string only at the last minute for presentation back to the user.