Solved

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

Posted on 2014-01-15
5
399 Views
Last Modified: 2014-01-31
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
0
Comment
Question by:motioneye
5 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 39782043
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
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39782097
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
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39782795
SELECT CONCAT(empid, CONVERT(varchar(6), [Active], 112)) AS MonthID, *
FROM MY_CATW
0
 

Accepted Solution

by:
motioneye earned 0 total points
ID: 39811164
hello all,
I finally manage to resolve my own, thst 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
0
 

Author Closing Comment

by:motioneye
ID: 39823552
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now