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',
ELSE concat(year([Active]),mont
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',
ELSE concat(year([Active]),mont
END as MonthID, *
FROM MY_CATW
MTHID EMPID
201401 20010940
201401 20010940
SELECT CASE
WHEN len(month([Active])) =1
THEN empid+ concat(year([Active]),'0',
ELSE concat(year([Active]),mont
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',
ELSE concat(year([Active]),mont
END as MonthID, *
FROM MY_CATW
MTHID EMPID
201401 20010940
201401 20010940
Microsoft SQL ServerSQL
Last Comment
motioneye
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:
For details on converting date to char, check here:
http://www.w3schools.com/sql/func_convert.asp
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;
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
FROM MY_CATW
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
Open in new window
See http://sqlfiddle.com/#!6/297f0/2