Solved

tsql convert numeric to date time gives unexpected results

Posted on 2014-09-23
3
186 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
In this Micro Tutorial viewers will learn how to restore single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now