?
Solved

tsql convert numeric to date time gives unexpected results

Posted on 2014-09-23
3
Medium Priority
?
217 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 1000 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 49

Accepted Solution

by:
PortletPaul earned 1000 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

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

The following article is comprised of the pearls we have garnered deploying virtualization solutions since Virtual Server 2005 and subsequent 2008 RTM+ Hyper-V in standalone and clustered environments.
I don't know if many of you have made the great mistake of using the Cisco Thin Client model with the management software VXC. If you have then you are probably more then familiar with the incredibly clunky interface, the numerous work arounds, and …
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 configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
Suggested Courses

777 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