• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

Convert Columns with Day1, Day2, Day3... Day31 converted to Dates

My current table looks like this
WorkCentre  Century  Year   Month  Day1  Day2  Day3  Day4... Day31
A1000             20         11      3        25.1    25.2    30    15.8      14.9
A1000             20         11      4        30.1    30.2    30.2  30.1      15.8

I need to reorganize to look like this
WorkCentre Date         Available
A1000        03/01/2011   25.1
A1000        03/02/2011   25.2
A1000        03/03/2011   30

All the way to the last day of the month...

Really need help with this badly
0
Michael Katz
Asked:
Michael Katz
  • 4
  • 3
1 Solution
 
knightEknightCommented:
Create the new table first, with the second column being of DATETIME or SMALLDATETIME type.  Then do this in 31 separate queries:

insert into myNewTable
select WorkCenter, convert(varchar,[Century]) + convert(varchar,[Year]) +'-'+ convert(varchar,[month]) +'-01', Day1 as Available
from myCurrentTable

insert into myNewTable
select WorkCenter, convert(varchar,[Century]) + convert(varchar,[Year]) +'-'+ convert(varchar,[month]) +'-02', Day2 as Available
from myCurrentTable

insert into myNewTable
select WorkCenter, convert(varchar,[Century]) + convert(varchar,[Year]) +'-'+ convert(varchar,[month]) +'-03', Day3 as Available
from myCurrentTable

-- ... etc


insert into myNewTable
select WorkCenter, convert(varchar,[Century]) + convert(varchar,[Year]) +'-'+ convert(varchar,[month]) +'-31', Day31 as Available
from myCurrentTable


0
 
Kevin CrossChief Technology OfficerCommented:
If you want this in a single SQL statement, then try to use UNPIVOT:

select WorkCentre
     , [Date] = convert(date, convert(char(8), Century * 1000000 + [Year] * 10000 + Month * 100 + convert(int, replace([Day], 'Day', ''))), 112)
	 , Available
from your_table
unpivot (
   Available for [Day] in (Day1, Day2, Day3, Day4, Day31)
) upvt
;

Open in new window


One caveat to note is that your data needs to be clean to start with.  In other words, for April you should have NULL value for Day31 as there is not a 31st day of April.  UNPIVOT will handle the nulls correctly since you won't get a row for that in the result.

Note also, my solution is converting the date parts into a true date.  I tested on a SQL 2008 system, so went with DATE data type.  If you want the results displayed as MM/DD/YYYY, you can use CONVERT or instead of casting to a date, simply build each part of the string as above Expert showed.
0
 
Michael KatzAuthor Commented:
Here is my query...
 select WorkCentre
     , [Date] = convert(date, convert(char(8), CapacityCentury * 1000000 + [CapacityYear] * 10000 + CapacityMonth * 100 + convert(int, replace([Day], 'Day', ''))), 112)
       , Available
from dbo.BomCapacity
unpivot (
    Available for [Day] in (CapacityAvail1,
CapacityAvail2,
CapacityAvail3,
CapacityAvail4,
CapacityAvail5,
CapacityAvail6,
CapacityAvail7,
CapacityAvail8,
CapacityAvail9,
CapacityAvail10,
CapacityAvail11,
CapacityAvail12,
CapacityAvail13,
CapacityAvail14,
CapacityAvail15,
CapacityAvail16,
CapacityAvail17,
CapacityAvail18,
CapacityAvail19,
CapacityAvail20,
CapacityAvail21,
CapacityAvail22,
CapacityAvail23,
CapacityAvail24,
CapacityAvail25,
CapacityAvail26,
CapacityAvail27,
CapacityAvail28,
CapacityAvail29,
CapacityAvail30,
CapacityAvail31)
) upvt
;


This is the message i get

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'CapacityAvail1' to data type int.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Kevin CrossChief Technology OfficerCommented:
That is because your values aren't really Day1, Day2, ... Day31.  Therefore, replacing 'Day' doesn't work.  You will need to replace 'CapacityAvail'.  Remember the point there is to get the number isolated so you can convert to an integer.
select WorkCentre
     , [Date] = convert(date, convert(char(8), CapacityCentury * 1000000 + [CapacityYear] * 10000 + CapacityMonth * 100 + convert(int, replace([Day], 'CapacityAvail', ''))), 112)
       , Available
from dbo.BomCapacity
unpivot (
    Available for [Day] in (CapacityAvail1, 
    CapacityAvail2,
    CapacityAvail3,
    CapacityAvail4,
    CapacityAvail5,
    CapacityAvail6,
    CapacityAvail7,
    CapacityAvail8,
    CapacityAvail9,
    CapacityAvail10,
    CapacityAvail11,
    CapacityAvail12,
    CapacityAvail13,
    CapacityAvail14,
    CapacityAvail15,
    CapacityAvail16,
    CapacityAvail17,
    CapacityAvail18,
    CapacityAvail19,
    CapacityAvail20,
    CapacityAvail21,
    CapacityAvail22,
    CapacityAvail23,
    CapacityAvail24,
    CapacityAvail25,
    CapacityAvail26,
    CapacityAvail27,
    CapacityAvail28,
    CapacityAvail29,
    CapacityAvail30,
    CapacityAvail31)
) upvt
;

Open in new window

0
 
Michael KatzAuthor Commented:
mwvisa1,

I am still getting the error.. What I am I not doing correctly??..

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
0
 
Michael KatzAuthor Commented:
knightEknight,

This Table you showed me doesnt appear to be run everyday...Did I miss something?
0
 
Kevin CrossChief Technology OfficerCommented:
@Mikeyman_01:

That would suggest you have values that don't equate to a proper date.  It is probably what I was talking about with dates like 4/31 that don't really exist.  Let's try like this with a test first.

+Get all the data without converting to actual DATE
+Using the below as a view, derived table or common table expression, select only those rows where ISDATE([DATE]) = 1.

select WorkCentre
     , [Date] = convert(char(8), CapacityCentury * 1000000 + [CapacityYear] * 10000 + CapacityMonth * 100 + convert(int, replace([Day], 'CapacityAvail', '')))
     , Available
from dbo.BomCapacity
unpivot (
    Available for [Day] in (CapacityAvail1, 
    CapacityAvail2,
    CapacityAvail3,
    CapacityAvail4,
    CapacityAvail5,
    CapacityAvail6,
    CapacityAvail7,
    CapacityAvail8,
    CapacityAvail9,
    CapacityAvail10,
    CapacityAvail11,
    CapacityAvail12,
    CapacityAvail13,
    CapacityAvail14,
    CapacityAvail15,
    CapacityAvail16,
    CapacityAvail17,
    CapacityAvail18,
    CapacityAvail19,
    CapacityAvail20,
    CapacityAvail21,
    CapacityAvail22,
    CapacityAvail23,
    CapacityAvail24,
    CapacityAvail25,
    CapacityAvail26,
    CapacityAvail27,
    CapacityAvail28,
    CapacityAvail29,
    CapacityAvail30,
    CapacityAvail31)
) upvt
;

Open in new window

0
 
Michael KatzAuthor Commented:
mwvisa1,

Excellent Job!! Thank you very much...

You helped me tremendously..

Michael
0
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

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now