convert Ms access query to Sql server 2008

Question

0.00/5 (No votes)

See more:

This is Qyery in ms Access how to write and execute this in Sql server 2008

SQL

select 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
                            CDate(IIF(isnull(SuppliedDate),'01/01/1990',SuppliedDate))=CDate(' 11/30/2013')
OR
  ( ( CDate(IIF(isnull(SuppliedDate),'01/01/1990',SuppliedDate))  and  (CDate(IIF(isnull(vr_date),'01/01/1990',vr_date)) >= CDate('11/30/2013')-1 AND CDate(IIF(isnull(vr_date),'01/01/1990',vr_date))
 <= CDate('11/30/2013'))) and AdminEdit='0') order by vrno desc

Posted 16-Dec-13 21:33pm

SwatiPotdar

Updated 16-Dec-13 21:36pm

Maciej Los

v2

Add a Solution

Comments

Amir Mahfoozi 17-Dec-13 3:56am

In this case everything is like SQL Server but for "IIF(ISNULL" part you can replace it with COALESCE function.

1 solution

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)

Christian Graus · Answer 1 · 2013-12-16T22:03:00

Solution 1

SQL

CDate(IIF(isnull(SuppliedDate),'01/01/1990',SuppliedDate))

becomes

SQL

coalesce(SuppliedDate, '1900/01/01')

This returns supplied date, or the constant, if supplied date is null.

It seems you want to convert an int to a date. You can't do that in SQL Server. CDate also does not exist ( as I keep saying ). Here is a page listing possible conversions and if you need to make them explicit:

http://msdn.microsoft.com/en-us/library/ms187928.aspx[^]

Posted 16-Dec-13 22:03pm

Christian Graus

Updated 17-Dec-13 8:35am

v3

Comments

Maciej Los 17-Dec-13 4:11am

5ed!

[no name] 17-Dec-13 4:22am

No itz not working in sql server 2008

Christian Graus 17-Dec-13 4:27am

Instead of saying 'it's not working', try reading the error messages, or posting the SQL you're trying and the error message. IF you want us to do your job for you, it helps if you don't ask us to read your mind.

[no name] 17-Dec-13 4:44am

when i tried this CDate(IIF(isnull(SuppliedDate),'01/01/1990',SuppliedDate)) to change

CDate(IIF(isnull(SuppliedDate),'01/01/1990',SuppliedDate)) its not showing result . in sqlserver2008

Christian Graus 17-Dec-13 4:46am

OK, I was not clear. CDate does not work. Don't use it, it is not needed, and does not work. That's why your error message is telling you it is wrong.

Christian Graus 17-Dec-13 4:48am

Of course, you ignored what I said altogether. Use coalesce. Not IIF. Not isnull ( although I think that works ). Use JUST coalesce. I suggest you edit your question after that, to give us the EXACT FULL sql you are using and the EXACT FULL error message, so we can explain what it is telling you, and fix your SQL further

Maciej Los 17-Dec-13 6:30am

Holy true!

[no name] 17-Dec-13 4:48am

then can you please tell me how to write this query into sql server 2008

select 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
(CDate(IIF(isnull(SuppliedDate),'01/01/1990',SuppliedDate))=CDate('" + txtFromDate.Text + "') OR ((CDate(IIF(isnull(SuppliedDate),'01/01/1990',SuppliedDate)) and (CDate(IIF(isnull(vr_date),'01/01/1990',vr_date)) >= CDate('" + txtFromDate.Text + "')-1 AND CDate(IIF(isnull(vr_date),'01/01/1990',vr_date)) <= CDate('" + txtFromDate.Text + "'))) and AdminEdit='0')) AND UserId=" + Uid + " order by vrno desc

Christian Graus 17-Dec-13 4:50am

If you can't even try to follow simple instructions, then no, I won't help further. I told you what to do. If you do it and have more issues, try asking REAL questions ( giving us DETAILS ), and I will help more.

[no name] 17-Dec-13 4:51am

but when i used that it is showing error Cdate does not exsist

Christian Graus 17-Dec-13 5:13am

Bloody hell. I've told you several times now. The error is correct. CDate is not SQL. Get rid of it.

Pete O'Hanlon 17-Dec-13 7:06am

You will be notified that I am deleting your answer. Personal abuse like this is not welcome on CodeProject. If you do not learn to modulate your behaviour, you will be removed from CodeProject.

Maciej Los 17-Dec-13 6:33am

Don't be nervous, Christian. Abuse is more and more often form of discuss ;(

[no name] 17-Dec-13 5:20am

showing error in sql server 2008 CDATE is nort recognized built i function name

CDate(IIF(isnull(vr_date,''),'01/01/1990',vr_date

Maciej Los 17-Dec-13 5:41am

Use: coalesce(vr_date, '1900/01/01') instead of your code.

[no name] 17-Dec-13 5:59am

sir but itz in integer format i want date format
convert( datetime, CASE WHEN isnull(vR_Date,'')='' THEN '01/01/1990' ELSE vR_Date END, 101)>

Maciej Los 17-Dec-13 6:28am

What?
Please update your question (use: "Improve question" widget) and provide more details about your issue, including sample data and expected output.

Christian Graus 17-Dec-13 14:30pm

There is no conversion from int to date. If there is in Access, then that is retarded.

[no name] 18-Dec-13 0:50am

thanks

[no name] 17-Dec-13 15:39pm

And the story is: Do not answer a question which is simply diligence work