sql proc date concat query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

Create Proc ProcDate 

as

Begin

Select

td.id , tod.OrderDetails  , td.[REPLACE(CONVERT(VARCHAR(20),DATEFROM,106),' ','-') + ' TO ' + REPLACE(CONVERT(VARCHAR(20),DATETO,106),' ','-')] AS [TOTAL ORDER RANGE DATE] 

FROM  tblDate td INNER JOIN tblOrderDetails tod 

ON   td.id = tod.id 

END

While running above query i am getting the following error as '

Invalid column name 'REPLACE(CONVERT(VARCHAR(20),DATEFROM,106),' ','-') + ' TO ' + REPLACE(CONVERT(VARCHAR(20),DATETO,106),' ','-')'.

Posted 26-Oct-12 21:39pm

lalitkr

Updated 26-Oct-12 21:43pm

ridoy

v2

Add a Solution

4 solutions

Solution 1

i am assuming that datefrom and dateto are in tblDate table.

now try below code :

SQL

Create Proc ProcDate

as

Begin

Select

td.id , tod.OrderDetails  , [REPLACE(CONVERT(VARCHAR(20),td.DATEFROM,106),' ','-') + ' TO ' + REPLACE(CONVERT(VARCHAR(20),td.DATETO,106),' ','-')] AS [TOTAL ORDER RANGE DATE]

FROM  tblDate td INNER JOIN tblOrderDetails tod

ON   td.id = tod.id

END

Posted 26-Oct-12 21:45pm

Bhushan Shah1988

Solution 3

I have Two Table tblOrder and tblOrderDetails

For the TblDate Having data as

Column_name Type
Id int
DateTo datetime
DateFrom datetime
OrderNumber varchar (50)

For the table tblOrderDetails

Column_name Type
id int
OrderDetails varchar(50)

I am Creating a Stored Proc on these two tables and fetching the result as shown in the proc and in result i want to merge the DateTo and DateFrom and show the result in the new column which not exist and also data not exist

Sample Data i am using

select * from tblOrderDetails
select * from tblDate

id OrderDetails
1 400
2 500
3 450
4 580

Id DateTo DateFrom OrderNumber
1 2012-10-27 07:55:35.857 2012-10-28 07:55:35.857 2222
11 2012-10-29 07:56:00.263 2012-10-31 07:56:00.263 1254
2 2012-11-07 08:13:41.247 2012-10-31 08:13:41.247 154

Posted 26-Oct-12 21:59pm

lalitkr

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)

Bhushan Shah1988 · Accepted Answer · 2012-10-26T22:39:00

try below code :

SQL

Create Proc ProcDate
 
as
 
Begin
 
Select
 
td.id , tod.OrderDetails  , REPLACE(CONVERT(VARCHAR(20),td.DATEFROM,106),' ','-') + ' TO ' + REPLACE(CONVERT(VARCHAR(20),td.DATETO,106),' ','-') AS [TOTAL ORDER RANGE DATE]
 
FROM  tblDate td INNER JOIN tblOrderDetails tod
 
ON   td.id = tod.id
 
END

mark as solution if it is helpful to you.

lalitkr · Accepted Answer · 2012-10-26T21:47:00

Solution 2

Showing following error again

Invalid column name 'REPLACE(CONVERT(VARCHAR(20),td.DATEFROM,106),' ','-') + ' TO ' + REPLACE(CONVERT(VARCHAR(20),td.DATETO,106),' ','-')'.

Posted 26-Oct-12 21:47pm

lalitkr

Comments

Bhushan Shah1988 27-Oct-12 3:48am

Please tell me in which table Dateto and Datefrom are??

lalitkr 27-Oct-12 4:06am

This both the DateTo and DateFrom are from the same table TblDate

Sergey Alexandrovich Kryukov 16-May-13 11:39am

This not an answer, this is fake. And self-accepting it as an answer is just cheating.
—SA