November 20, 2012 at 10:53 am
HI,
I have 2 derived columns and when i try to contatenate them I get error.
I used +' '+ to concatenate.
Thanks
Sm
November 20, 2012 at 10:58 am
the code is as follows
SELECT
CASE WHEN I353_DATE_TRANSACTION > 19000000 AND I353_DATE_TRANSACTION < 21000000 THEN
CONVERT(VARCHAR(12),
CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(INT,I353_DATE_TRANSACTION)))
,101)
ELSE '01/01/1900'
END AS DATE,
SUBSTRING(CONVERT(VARCHAR(12),1000000 + [I353_TIME_TRANSACTION]),2,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(12),1000000 + [I353_TIME_TRANSACTION]),4,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(12),1000000 + [I353_TIME_TRANSACTION]),6,2)as TIME
FROM My_TABLE
When I try concatenating 2 COLUMNS DATE+" '+TIME..it gives error
Thx
Sm
November 20, 2012 at 11:05 am
the 'END' after the first convert stuff then has an 'AS DATE'. This is just sort of applying a name to the expression. Remove the 'AS DATE' then try the concatenation and see how that works!
SELECT
CASE WHEN I353_DATE_TRANSACTION > 19000000 AND I353_DATE_TRANSACTION < 21000000 THEN
CONVERT(VARCHAR(12),
CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(INT,I353_DATE_TRANSACTION)))
,101)
ELSE '01/01/1900'
END + ' ' + -------- <<<<< notice I'm putting your + ' ' + here!
SUBSTRING(CONVERT(VARCHAR(12),1000000 + [I353_TIME_TRANSACTION]),2,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(12),1000000 + [I353_TIME_TRANSACTION]),4,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(12),1000000 + [I353_TIME_TRANSACTION]),6,2)as DATE_AND_TIME
FROM My_TABLE
November 20, 2012 at 11:10 am
Thank you again Patrick,..I got what I was looking for..:-)
November 20, 2012 at 11:15 am
This is the third thread you've started on this same issue. People, including me, have given you solutions on some threads when you have already been given as good or better solutions on the main thread. Breaking this up just leads to wasted time for the folks here who are trying to help and makes it less likely that some folks will respond to you in the future. If you've asked a question on a thread already, please don't start another one with the same question. BTW Patrick's solution to your time calculation problem is better than the one I gave you.
November 20, 2012 at 11:42 am
David,
Thanks for your help.
This thread was created for concatenating 2 derived columns..if you read the subject of my previous thread ,its something else.
Thanks
SM
November 20, 2012 at 1:22 pm
SQL SERVER ROOKIE (11/20/2012)
David,Thanks for your help.
This thread was created for concatenating 2 derived columns..if you read the subject of my previous thread ,its something else.
Thanks
SM
The title of the thread doesn't really matter, it is the content. I have already looked at three different threads and the content is the same. All three should have been kept together as a single thread.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply