?
Solved

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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
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.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

569 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