Solved

Unexpected / Invalid date results

Posted on 2014-09-24
7
152 Views
Last Modified: 2014-09-25
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

 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 

Open in new window


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.
0
Comment
Question by:Leogal
7 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 40342861
What are the datatypes of those date parts? All integers, as it looks like?
You might be better off using the date convertion functions ( CONVERT(...) with format).
0
 

Author Comment

by:Leogal
ID: 40342867
I have modified the from date code to read like this:

,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]

Open in new window


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
0
 

Author Comment

by:Leogal
ID: 40342876
Olemo, the data parts (month, day, year) are all numeric 2
0
Technology Partners: 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!

 

Author Comment

by:Leogal
ID: 40342926
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

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 

Open in new window

0
 
LVL 80

Expert Comment

by:David Johnson, CD, MVP
ID: 40342950
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"
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40343248
I would still suggest using DATEADD() for this, just adding to the case expression to cover the 99 99 99 situation:
SELECT
      ParentPart
    , PSEXCT
    , PSEXDA
    , PSEXMO
    , PSEXYR
    , CASE WHEN PSEXCT + PSEXDA + PSEXMO + PSEXYR = 0 THEN CAST('19000101' AS datetime)
           WHEN PSEXDA = 99 AND PSEXMO = 99 AND PSEXYR = 99 THEN CAST('21991231' AS datetime) 
           ELSE DATEADD(DAY, (PSEXDA - 1), DATEADD(MONTH, (PSEXMO - 1), DATEADD(YEAR, (PSEXCT * 100) + PSEXYR, 0)))
      END
FROM table1
;

Open in new window

From this data
CREATE TABLE Table1
      ([ParentPart] varchar(20), [PSEXCT] int, [PSEXDA] int, [PSEXMO] int, [PSEXYR] int)
;
	
INSERT INTO Table1
	([ParentPart], [PSEXCT], [PSEXDA], [PSEXMO], [PSEXYR])
VALUES
	('ALK.10385-1', 1, 9, 8, 2),
	('ALK.10385-2', 1, 7, 8, 9),
	('ALK.10385-3', 0, 0, 0, 0)
;

INSERT INTO Table1
	([PSEXCT], [PSEXDA], [PSEXMO], [PSEXYR])
VALUES
	(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),
	(1, 31, 7, 7),
      (0, 0, 0, 0),
	(9, 99, 99, 99)
;

update table1
set ParentPart = 'New Question'

Open in new window

The results are:
|   PARENTPART | PSEXCT | PSEXDA | PSEXMO | PSEXYR |                        COLUMN_5 |
|--------------|--------|--------|--------|--------|---------------------------------|
| New Question |      1 |      9 |      8 |      2 |   August, 09 2002 00:00:00+0000 |
| New Question |      1 |      7 |      8 |      9 |   August, 07 2009 00:00:00+0000 |
| New Question |      0 |      0 |      0 |      0 |  January, 01 1900 00:00:00+0000 |
| New Question |      0 |     22 |     11 |     97 | November, 22 1997 00:00:00+0000 |
| New Question |      0 |      0 |      0 |      0 |  January, 01 1900 00:00:00+0000 |
| New Question |      1 |      7 |      8 |      9 |   August, 07 2009 00:00:00+0000 |
| New Question |      1 |     12 |      5 |      5 |      May, 12 2005 00:00:00+0000 |
| New Question |      1 |      9 |      8 |      2 |   August, 09 2002 00:00:00+0000 |
| New Question |      1 |      7 |      8 |      9 |   August, 07 2009 00:00:00+0000 |
| New Question |      0 |      0 |      0 |      0 |  January, 01 1900 00:00:00+0000 |
| New Question |      1 |     31 |      7 |      7 |     July, 31 2007 00:00:00+0000 |
| New Question |      0 |      0 |      0 |      0 |  January, 01 1900 00:00:00+0000 |
| New Question |      9 |     99 |     99 |     99 | December, 31 2199 00:00:00+0000 |
		

Open in new window

see it working here: http://sqlfiddle.com/#!3/37587/5
0
 

Author Closing Comment

by:Leogal
ID: 40344197
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!
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DC with error SChannel ID 36888 3 37
SQL Server Sum Over Multiple Tables 20 29
Logical Operator should return Integer value in SSIS 9 34
SQL 2014 missing dll from Bin? 3 31
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

679 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