Link to home
Create AccountLog in
Avatar of motioneye
motioneyeFlag for Singapore

asked on

SQL, Convert date time with other column produce different result set

I tried with SQL below to combine the empid with Active which as when I combine both column MTHID show me different results.

SELECT CASE
            WHEN len(month([Active])) =1  
               THEN empid+ concat(year([Active]),'0',month([Active]))
               ELSE concat(year([Active]),month([Active]))
       END as MonthID, *
FROM MY_CATW

MTHID           EMPID
20212341      20010940
20212341      20010940


With below SQL which I did not combine it appears correctly, so I expect when we combine both column it should have result like 20010940201401

SELECT CASE
            WHEN len(month([Active])) =1  
               THEN   concat(year([Active]),'0',month([Active]))
               ELSE concat(year([Active]),month([Active]))
       END as MonthID, *
FROM MY_CATW

MTHID   EMPID
201401      20010940
201401      20010940
Avatar of mankowitz
mankowitz
Flag of United States of America image

Try this:
SELECT CASE 
            WHEN len(month([Active])) =1  
               THEN concat(empid, year([Active]),'0',month([Active])) 
               ELSE concat(empid, year([Active]),month([Active])) 
       END as MonthID, * 
FROM MY_CATW

Open in new window


See http://sqlfiddle.com/#!6/297f0/2
strange... It seems you want to pad the month with 0 to be two digits always, but you want to prefix it with empid only if it needed the padding and not otherwise?!?
I believe mankowitz's solution makes sense if you *always* want to prefix with empid.
Also check this:

SELECT concat(empid, left(convert(varchar(10), [Active], 112),6) as MonthID, *
FROM MY_CATW;

Open in new window


For details on converting date to char, check here:
http://www.w3schools.com/sql/func_convert.asp
SELECT CONCAT(empid, CONVERT(varchar(6), [Active], 112)) AS MonthID, *
FROM MY_CATW
ASKER CERTIFIED SOLUTION
Avatar of motioneye
motioneye
Flag of Singapore image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of motioneye

ASKER

hello all,
I finally manage to resolve my own, that simply because the empid have data type float which would not able to convert to nvarchar.
I have change the empid table to nvarchar and the combination works fine with my ols scripts