Solved

tsql convert numeric to date time gives unexpected results

Posted on 2014-09-23
3
203 Views
Last Modified: 2014-09-24
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
Comment
Question by:Leogal
3 Comments
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 250 total points
ID: 40340231
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40340580
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
 

Author Closing Comment

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DNS zone 3 29
You Must Change password - RDS 2012 R2 5 17
ADFS Setup 4 19
Windows updates manual or  automatic 2 13
OfficeMate Freezes on login or does not load after login credentials are input.
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
In this Micro Tutorial viewers will learn how they can get their files copied out from their unbootable system without need to use recovery services. As an example non-bootable Windows 2012R2 installation is used which has boot problems.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

821 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