Michael Katz
asked on
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
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
If you want this in a single SQL statement, then try to use UNPIVOT:
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.
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
;
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.
ASKER
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.
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.
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
;
ASKER
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.
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.
ASKER
knightEknight,
This Table you showed me doesnt appear to be run everyday...Did I miss something?
This Table you showed me doesnt appear to be run everyday...Did I miss something?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mwvisa1,
Excellent Job!! Thank you very much...
You helped me tremendously..
Michael
Excellent Job!! Thank you very much...
You helped me tremendously..
Michael
insert into myNewTable
select WorkCenter, convert(varchar,[Century])
from myCurrentTable
insert into myNewTable
select WorkCenter, convert(varchar,[Century])
from myCurrentTable
insert into myNewTable
select WorkCenter, convert(varchar,[Century])
from myCurrentTable
-- ... etc
insert into myNewTable
select WorkCenter, convert(varchar,[Century])
from myCurrentTable