Solved

tsql convert numeric to date time gives unexpected results

Posted on 2014-09-23
3
208 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

739 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