Solved

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

Posted on 2014-01-15
5
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

738 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