Leogal
asked on
Unexpected / Invalid date results
I am struggling with a new task. I have been requested to take all raw data dates that are invalid and convert them to one of the following:
Make the 00/00/00 = 1901-01-01 and 99/99/99 = 2199/12/31. I also need to insert a zero in front of all single month and day fields.
I am failing miserably on this.
Here is the entire sql script, only the case statements reflect the dates I am trying to manipulate. The first set of case statements are from a table that the raw data for fields PSEFCT, PSEFDA, PSEFMO, PSEFYR looks like this:
EFCTV EFCTV EFCTV EFCTV
CT DY MO YR
0 22 11 97
0 0 0 0
1 7 8 9
1 12 5 5
1 9 8 2
1 7 8 9
0 0 0 0
0 0 0 0
The next set of case statements refer to raw data for the fields PSEXCT, PSEXDA, PSEXMO, PSEXYR and it looks like this:
EXP EXP EXP EXP
CT DY MO YR
1 31 7 7
9 99 99 99
9 99 99 99
9 99 99 99
9 99 99 99
Here is a sample of my results in the date field
0- 0-19 0
0- 0-19 0
0- 0-19 0
09-09-20 13
09-09-20 13
09-09-20 13
09-09-20 13
09-09-20 13
09-09-20 13
09-09-20 13
0- 0-19 0
0- 0-19 0
0- 0-19 0
0- 0-19 0
0- 0-19 0
11- 21-20 11
11- 21-20 11
11- 21-20 11
11- 21-20 11
11- 21-20 11
Can someone help me understand where I am falling off the pier? Thank you.
Make the 00/00/00 = 1901-01-01 and 99/99/99 = 2199/12/31. I also need to insert a zero in front of all single month and day fields.
I am failing miserably on this.
Here is the entire sql script, only the case statements reflect the dates I am trying to manipulate. The first set of case statements are from a table that the raw data for fields PSEFCT, PSEFDA, PSEFMO, PSEFYR looks like this:
EFCTV EFCTV EFCTV EFCTV
CT DY MO YR
0 22 11 97
0 0 0 0
1 7 8 9
1 12 5 5
1 9 8 2
1 7 8 9
0 0 0 0
0 0 0 0
The next set of case statements refer to raw data for the fields PSEXCT, PSEXDA, PSEXMO, PSEXYR and it looks like this:
EXP EXP EXP EXP
CT DY MO YR
1 31 7 7
9 99 99 99
9 99 99 99
9 99 99 99
9 99 99 99
SELECT
cast('YES' as nvarchar(3)) as [BOM_APPROVED]
,' ' as [BOM_APPROVERPARTYNAME]
,' ' as [BOM_APPROVERPARTYNUMBER]
,' ' as [BOM_APPROVERPERSONNELNUMBER]
,[PSPMRN] as [BOM_BOMID]
,' ' as [BOM_CHECKBOM]
,' ' as [BOM_ITEMGROUPID]
,[IMDSC] as[BOM_NAME]
,' ' as [BOM_ROUTE_CONFIGGROUPID]
,' ' as [BOM_ROUTE_LINENUM]
,' ' as [BOM_ROUTE_NAME]
,'Variable' as [BOMCONSUMP]
,CASE WHEN [PSQTY] > 0 THEN [PSQTY] ELSE 1 END as [BOMQTY]
,1 as [BOMQTYSERIE]
,CASE WHEN [IMPHAN] in ('N','P') THEN 'Phantom'
WHEN [IMPHAN] = ' ' THEN 'ITEM'
END as [BOMTYPE]
,'YES' as [CALCULATION]
,' ' as [CONFIGGROUPID]
,' ' as [CONFIGID]
,' ' as [CONSTANT]
,' ' as [DEFINITIONGROUP]
,' ' as [DENSITY]
,' ' as [DEPTH]
,' ' as [ENDSCHEDCONSUMP]
,' ' as [EXECUTIONID]
,' ' as [FORMULA]
,CASE WHEN LEN(PSEFMO) < 2 and PSEFMO <> 0 THEN '0' ELSE REPLACE(PSEFMO,PSEFMO,' ')END + CAST(PSEFMO as varchar(2)) + '-' +
CASE WHEN LEN(PSEFDA) < 2 and PSEFDA <> 0 THEN '0' ELSE ' ' END + CAST(PSEFDA as varchar(2)) + '-' +
CASE WHEN (PSEFCT = 0) THEN '19' ELSE '20' END +
CASE WHEN PSEFYR < 10 AND LEN(PSEFYR) = 1 and PSEFYR <> 0 THEN '0' ELSE ' ' END + CAST(PSEFYR as varchar(2))
AS [FROMDATE]
,' ' as [INVENTBATCHID]
,' ' as [INVENTCOLORID]
,' ' as [INVENTDIMID]
,' ' as [INVENTLOCATIONID]
,' ' as [INVENTSERIALID]
,'01' as [INVENTSITEID]
,' ' as [INVENTSIZEID]
,' ' as [INVENTSTYLEID]
,' ' as [ISSELECTED]
,' ' as [ITEMBOMID]
,[PSPMRN] as [ITEMID]
,' ' as [ITEMPBAID]
,' ' as [ITEMROUTEID]
,CASE WHEN [PSSEQ] > 0 THEN [PSSEQ] ELSE 1 END as [LINENUM]
,' ' as [OPRNUM]
,' ' as [PDSBASEVALUE]
,' ' as [PDSCWQTY]
,' ' as [PDSINGREDIENTTYPE]
,' ' as [PDSINHERITCOPRODUCTBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE]
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITENDITEMSHELFLIFE]
,' ' as [PMFFORMULAPCT]
,' ' as [PMFPCTENABLE]
,' ' as [PMFPLANGROUPID]
,' ' as [PMFPLANGROUPPRIORITY]
,' ' as [PMFSCALABLE]
,' ' as [POSITION]
,'FINISH' as [PRODFLUSHINGPRINCIP]
,' ' as [PROJSETSUBPRODTOCONSUMED]
,' ' as [ROUNDUP]
,' ' as [ROUNDUPQTY]
,' ' as [ROWID]
,' ' as [SCRAPCONST]
,' ' as [SCRAPVAR]
,CASE WHEN PSEXMO <> 99 AND LEN(PSEXMO) = 1 THEN '0' ELSE '' END + CAST(PSEXMO as varchar(2)) + '-' +
CASE WHEN PSEXDA >0 AND LEN(PSEXDA) < 2 THEN '0' ELSE '' END + CAST(PSEXDA as varchar(2)) + '-' +
CASE WHEN PSEXCT = 9 THEN '19' ELSE '20' END +
CASE WHEN PSEXYR >0 and PSEXYR <> 99 AND LEN(PSEXYR) < 2 THEN '0' ELSE '' END + CAST(PSEXYR as varchar(2))
as [TODATE]
,' ' as [TRANSFERSTATUS]
,' ' as [UNITID]
,' ' as [VENDID]
,' ' as [WIDTH]
,' ' as [WMSLOCATIONID]
,' ' as [WMSPALLETID]
,' ' as [WRKCTRCONSUMPTION]
,1 as [RECVERSION]
,1 as [PARTITION]
,1 as [RECID]
FROM OPENQUERY
(KBM400MFG,
' SELECT
[PSPMRN]
,[IMDSC]
,[PSQTY]
,[PSEFCT]
,[IMPHAN]
,[PSEFDA]
,[PSEFMO]
,[PSEFYR]
,[PSSEQ]
,[PSEXDA]
,[PSEXMO]
,[PSEXYR]
,PSCMRN
,PSEXCT
FROM [KBM400MFG].[FKITMSTR] B
INNER JOIN [S1022466].[KBM400MFG].[FKPSTRUC] C on B.IMPN = C.PSPMRN and B.IMCO = C.PSCO
'
) AS A
Here is a sample of my results in the date field
0- 0-19 0
0- 0-19 0
0- 0-19 0
09-09-20 13
09-09-20 13
09-09-20 13
09-09-20 13
09-09-20 13
09-09-20 13
09-09-20 13
0- 0-19 0
0- 0-19 0
0- 0-19 0
0- 0-19 0
0- 0-19 0
11- 21-20 11
11- 21-20 11
11- 21-20 11
11- 21-20 11
11- 21-20 11
Can someone help me understand where I am falling off the pier? Thank you.
ASKER
I have modified the from date code to read like this:
then I get results like this, the first field is the incoming month, the second field is incoming day, the third field shows incoming year and the FROMDATE is the resulting output from the code above. I am working on why the one row of data
showing 5 12 5 (mm/dd/yy) is outputting null.
PSEFMO psefda PSEFYR FROMDATE
0 0 0 00-00-1900
0 0 0 00-00-1900
0 0 0 00-00-1900
0 0 0 00-00-1900
5 12 5 NULL
8 9 2 08-09-2002
8 7 9 08-07-2009
0 0 0 00-00-1900
,CASE WHEN LEN(PSEFMO) = 1 THEN '0' END + CAST(PSEFMO as varchar(2)) + '-' +
CASE WHEN LEN(PSEFDA) = 1 THEN '0' END + CAST(PSEFDA as varchar(2)) + '-' +
CASE WHEN (PSEFCT = 0) THEN '19' ELSE '20' END +
CASE WHEN LEN(PSEFYR) = 1 THEN '0' END + CAST(PSEFYR as varchar(2))
AS [FROMDATE]
then I get results like this, the first field is the incoming month, the second field is incoming day, the third field shows incoming year and the FROMDATE is the resulting output from the code above. I am working on why the one row of data
showing 5 12 5 (mm/dd/yy) is outputting null.
PSEFMO psefda PSEFYR FROMDATE
0 0 0 00-00-1900
0 0 0 00-00-1900
0 0 0 00-00-1900
0 0 0 00-00-1900
5 12 5 NULL
8 9 2 08-09-2002
8 7 9 08-07-2009
0 0 0 00-00-1900
ASKER
Olemo, the data parts (month, day, year) are all numeric 2
ASKER
I made changes to the FROMDATE code and now I am getting good results, albeit not quite there. I still need to convert the returned date of 00-00-1900 to 01-01-1900.
Here is a sample of my returned FROMDATE results with the individual incoming data fields.
PSEFMO psefda PSEFYR FROMDATE
11 18 11 11-18-2011
5 28 14 05-28-2014
0 0 0 00-00-1900
0 0 0 00-00-1900
0 0 0 00-00-1900
2 3 14 02-03-2014
0 0 0 00-00-1900
0 0 0 00-00-1900
Here are the results of my TODATE with their individual incoming field data
psexmo psexda psexyr TODATE
7 31 7 7-31-2007
99 99 99 99-99-1999
99 99 99 99-99-1999
99 99 99 99-99-1999
99 99 99 99-99-1999
All date incoming date fields are numeric 2 positions.
Now I still need help to understand how I can make this requested change:
Make the 00/00/00 = 1901-01-01 and 99/99/99 = 2199/12/31. I am pretty close but not there...
Here is a clean revised version of my sql script
Here is a sample of my returned FROMDATE results with the individual incoming data fields.
PSEFMO psefda PSEFYR FROMDATE
11 18 11 11-18-2011
5 28 14 05-28-2014
0 0 0 00-00-1900
0 0 0 00-00-1900
0 0 0 00-00-1900
2 3 14 02-03-2014
0 0 0 00-00-1900
0 0 0 00-00-1900
Here are the results of my TODATE with their individual incoming field data
psexmo psexda psexyr TODATE
7 31 7 7-31-2007
99 99 99 99-99-1999
99 99 99 99-99-1999
99 99 99 99-99-1999
99 99 99 99-99-1999
All date incoming date fields are numeric 2 positions.
Now I still need help to understand how I can make this requested change:
Make the 00/00/00 = 1901-01-01 and 99/99/99 = 2199/12/31. I am pretty close but not there...
Here is a clean revised version of my sql script
SELECT
cast('YES' as nvarchar(3)) as [BOM_APPROVED]
,' ' as [BOM_APPROVERPARTYNAME]
,' ' as [BOM_APPROVERPARTYNUMBER]
,' ' as [BOM_APPROVERPERSONNELNUMBER]
,[PSPMRN] as [BOM_BOMID]
,' ' as [BOM_CHECKBOM]
,' ' as [BOM_ITEMGROUPID]
,[IMDSC] as[BOM_NAME]
,' ' as [BOM_ROUTE_CONFIGGROUPID]
,' ' as [BOM_ROUTE_LINENUM]
,' ' as [BOM_ROUTE_NAME]
,'Variable' as [BOMCONSUMP]
,CASE WHEN [PSQTY] > 0 THEN [PSQTY] ELSE 1 END as [BOMQTY]
,1 as [BOMQTYSERIE]
,CASE WHEN [IMPHAN] in ('N','P') THEN 'Phantom'
WHEN [IMPHAN] = ' ' THEN 'ITEM'
END as [BOMTYPE]
,'YES' as [CALCULATION]
,' ' as [CONFIGGROUPID]
,' ' as [CONFIGID]
,' ' as [CONSTANT]
,' ' as [DEFINITIONGROUP]
,' ' as [DENSITY]
,' ' as [DEPTH]
,' ' as [ENDSCHEDCONSUMP]
,' ' as [EXECUTIONID]
,' ' as [FORMULA]
, PSEFMO
, psefda
,PSEFYR
,CASE WHEN LEN(PSEFMO) = 1 THEN CONCAT(0,PSEFMO) else CAST(PSEFMO as varchar(2)) END + '-' +
CASE WHEN LEN(PSEFDA) = 1 THEN CONCAT(0,PSEFDA) else CAST(PSEFDA as varchar(2)) END + '-' +
CASE WHEN (PSEFCT = 0) THEN '19' ELSE '20' END +
CASE WHEN LEN(PSEFYR) =1 THEN '00' ELSE CAST(PSEFYR as varchar(2)) END
AS [FROMDATE]
,' ' as [INVENTBATCHID]
,' ' as [INVENTCOLORID]
,' ' as [INVENTDIMID]
,' ' as [INVENTLOCATIONID]
,' ' as [INVENTSERIALID]
,'01' as [INVENTSITEID]
,' ' as [INVENTSIZEID]
,' ' as [INVENTSTYLEID]
,' ' as [ISSELECTED]
,' ' as [ITEMBOMID]
,[PSPMRN] as [ITEMID]
,' ' as [ITEMPBAID]
,' ' as [ITEMROUTEID]
,CASE WHEN [PSSEQ] > 0 THEN [PSSEQ] ELSE 1 END as [LINENUM]
,' ' as [OPRNUM]
,' ' as [PDSBASEVALUE]
,' ' as [PDSCWQTY]
,' ' as [PDSINGREDIENTTYPE]
,' ' as [PDSINHERITCOPRODUCTBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE]
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITENDITEMSHELFLIFE]
,' ' as [PMFFORMULAPCT]
,' ' as [PMFPCTENABLE]
,' ' as [PMFPLANGROUPID]
,' ' as [PMFPLANGROUPPRIORITY]
,' ' as [PMFSCALABLE]
,' ' as [POSITION]
,'FINISH' as [PRODFLUSHINGPRINCIP]
,' ' as [PROJSETSUBPRODTOCONSUMED]
,' ' as [ROUNDUP]
,' ' as [ROUNDUPQTY]
,' ' as [ROWID]
,' ' as [SCRAPCONST]
,' ' as [SCRAPVAR]
, psexmo
, psexda
, psexyr
,CASE WHEN PSEXMO > 99 AND LEN(PSEXMO) = 1 THEN '0' ELSE '' END + CAST(PSEXMO as varchar(2)) + '-' +
CASE WHEN PSEXDA >0 AND LEN(PSEXDA) < 2 THEN '0' ELSE '' END + CAST(PSEXDA as varchar(2)) + '-' +
CASE WHEN PSEXCT = 9 THEN '19' ELSE '20' END +
CASE WHEN PSEXYR >0 and PSEXYR <> 99 AND LEN(PSEXYR) < 2 THEN '0' ELSE '' END + CAST(PSEXYR as varchar(2))
as [TODATE]
,' ' as [TRANSFERSTATUS]
,' ' as [UNITID]
,' ' as [VENDID]
,' ' as [WIDTH]
,' ' as [WMSLOCATIONID]
,' ' as [WMSPALLETID]
,' ' as [WRKCTRCONSUMPTION]
,1 as [RECVERSION]
,1 as [PARTITION]
,1 as [RECID]
FROM OPENQUERY
(KBM400MFG,
' SELECT
[PSPMRN]
,[IMDSC]
,[PSQTY]
,[PSEFCT]
,[IMPHAN]
,[PSEFDA]
,[PSEFMO]
,[PSEFYR]
,[PSSEQ]
,[PSEXDA]
,[PSEXMO]
,[PSEXYR]
,PSCMRN
,PSEXCT
FROM [KBM400MFG].[FKITMSTR] B
INNER JOIN [S1022466].[KBM400MFG].[FKPSTRUC] C on B.IMPN = C.PSPMRN and B.IMCO = C.PSCO
'
) AS A
create a special case for those 2 items
if day = 0 and month = 0 and year= 1900 then date="01/01/1900"
if day=99 and month = 99 and year = 99 then date= "12/31/2199"
if day = 0 and month = 0 and year= 1900 then date="01/01/1900"
if day=99 and month = 99 and year = 99 then date= "12/31/2199"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Portlet Paul, this is an elegant solution for a difficult problem. I am going to have to study the function DATEADD and start applying it more often. Thanks again!
You might be better off using the date convertion functions ( CONVERT(...) with format).