?
Solved

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

Posted on 2014-01-15
5
Medium Priority
?
431 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 70

Expert Comment

by:Scott Pletcher
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

589 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