[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

tsql convert numeric to date time gives unexpected results

I have been stumped for 3 days now trying to figure out what is causing the conversion issues when I try to convert three data fields (all 2 position numeric) to give such skewed results.  

I have three fields PSEFDA = day of month
                                 PSEFMO = month
                                 PSEFYR = year

I am trying to concatenate them (mm *100  + dd + yy) and then convert them to datetime, giving a result of
1900-01-01 00:00:00:000 if the fields are zeros or the actual date.

There is a century field seems to be  0 = 1900's
                                                                  1= 2000
I realize that it would be helpful to use this field to determine the correct century for the year but I thought I would at least get the dates working first.

The odd thing is the dates in the file show one thing and the results from query show another.
In example:

Here  is a screen print of raw data:

data screen
Parent Part ALK.10385 , component part # SPEC-0047 shows in the raw data as century =1 , month =8,  day = 9 and year =2  so effectively the date is 08-09-2002.  On my results after running the script below  the same exact part & component gives a date of 1902-03-23.  I am sure that I am not the first to have this occur but I sure do not know what I am doing wrong. Any help would certainly be appreciated.

I have a 2nd field in this data table that I need to convert too, but I have not gotten that far...

here is the query
 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]
				   ,PSCMRN 
				   ,pspmrn  ------------------------------------------------------------------------------
			 
					,CAST(
						   PSEFMO *100 +
						  RIGHT('0' + CAST(PSEFDA AS VARCHAR(2)), 2) +
						     PSEFYR *10000   
					   AS DATETIME)
 				          
				   ,' ' 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]
				   ,CONCAT([PSEXDA], ' ',[PSEXMO],' ',[PSEXYR]) 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
				                      
				 
	 
   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

test.rpt
0
Leogal
Asked:
Leogal
2 Solutions
 
Shaun KlineLead Software EngineerCommented:
You may get better results by putting your data in an MM/DD/YY format.
Something like:
CAST(PSEFMO as varchar(2)) + '/' + CAST(PSEFDA AS VARCHAR(2)) + '/' + CAST(PSEFYR as varchar(2)) AS DATETIME)

Open in new window


You should also make sure your Year has at minimum two digits.

When you want to handle century, just add a CASE statement prior to the CAST statement for the year.

Another good date format to use is YYYY-MM-DD.
0
 
PortletPaulCommented:
I would use DATEADD(), when using this keep in mind that zero is 1900-01-01 so (for example) if you want the 7th month as a result, you add only 6 months, hence you will see minus one when this is needed.

This small sample:
|  PARENTPART | PSEFCT | PSEFDA | PSEFMO | PSEFYR |                       COLUMN_5 |
|-------------|--------|--------|--------|--------|--------------------------------|
| ALK.10385-1 |      1 |      9 |      8 |      2 |  August, 09 2002 00:00:00+0000 |
| ALK.10385-2 |      1 |      7 |      8 |      9 |  August, 07 2009 00:00:00+0000 |
| ALK.10385-3 |      0 |      0 |      0 |      0 | January, 01 1900 00:00:00+0000 |

Open in new window

Was produced from this data:
CREATE TABLE Table1
	([ParentPart] varchar(20), [PSEFCT] int, [PSEFDA] int, [PSEFMO] int, [PSEFYR] int)
;
	
INSERT INTO Table1
	([ParentPart], [PSEFCT], [PSEFDA], [PSEFMO], [PSEFYR])
VALUES
	('ALK.10385-1', 1, 9, 8, 2),
	('ALK.10385-2', 1, 7, 8, 9),
	('ALK.10385-3', 0, 0, 0, 0)
;

Open in new window

Using this query:
select [ParentPart], [PSEFCT], [PSEFDA], [PSEFMO], [PSEFYR]


, case when PSEFCT+PSEFDA+PSEFMO+PSEFYR = 0 then dateadd(day,0,0)
       else
            dateadd(day,(PSEFDA-1),dateadd(month,(PSEFMO-1),dateadd(year,(PSEFCT*100)+PSEFYR,0)))
  end


from table1

Open in new window

also see: http://sqlfiddle.com/#!3/43c46/1

BTW: if you have SQL Server 2012 you could use DATEFROMPARTS()
0
 
LeogalAuthor Commented:
I am truly appreciative of the very insightful help that I receive here on Experts Exchange.  I send my thanks to Shaun Kline and Portlet Paul for their timely assistance.
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

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