I forgot to meantion that datatype of MY_DATE_COLUMN should have been datetime ,for the code below I'm getting the result in the right format..ie but the column MY_DATE_COLUMN has...

You are right..thank u

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

Thank you again Patrick,..I got what I was looking for..:-)

I tried concatenting the derived columns using +' '+, unfortunately it gives error

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 DATE +' '+SUBSTRING(CONVERT(VARCHAR(12),1000000 + [I353_TIME_TRANSACTION]),2,2)...

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) + ':' +

...

ok..Will keep that in mind

Thank u for the tip 🙂

Patrick,

Thank you one more time for bailing me out.Even this works perfectly like the one you gave before.

Thnx

Swarup

Patick,

One small doubt.I have to concatenate one more time_column to this date_column.

This time column is also defined as a decimal ,I'm sure even its code is gonna be similar to...

Patrick,

Thanks Buddy..you are the man ..the code worked like a charm.Can't thank you enough,I have been beating my head to solve this.

Regards

Swarup

Splendid Patrick,

That is what I was looking for, Only thing is the out put is in YYYY-MM-DD format , how can I change it to MM/DD/YYYY..and the out is NEW_DATE_COLUMN

2012-09-10...

I have broken down the reqs into 3 parts

1)Cast decimal as string ( which I did)

select convert(varchar,convert(decimal(8,0),[I353_DATE_TRANSACTION]))

from [stgFACTS].[dbo].[I353_SalesOrder]

2), parse into the format specified(which is MM/DD/YYYY....I don’t know how...

in the above code the date column is [I353_DATE_TRANSACTION]

I have deleted a lot of columns names..while pasting it ..