date issues using convert in TSQL

I have a difficult problem with date for me.

my input date looks like this :120307

the field, name and attributes look like this: PODATE ,DECIMAL(6, 0) NOT NULL DEFAULT 0 ,

I am converting the date using convert(Datetime, field,style) See- code below:
I get an invalid returned date:  2229-05-23 00:00:00.000
 
What can I do to correct this so the date output is 2007-12-03 00:00:00.000 ?

USE DmStagingVD

Delete from dbo.DMFPURCHTABLEENTITY2

insert into dbo.DMFPURCHTABLEENTITY2

 ( 
        [ACCOUNTINGDATE]
	  , [ACCOUNTINGDISTRIBUTIONTEMPLATE]
	  , [AUTOSUMMARYMODULETYPE]
      , [AVAILSALESDATE]
	  , [BANKCENTRALBANKPURPOSECODE]
	  , [BANKCENTRALBANKPURPOSETEXT]
	  , [BANKDOCUMENTTYPE]
	  , [CASHDISC]
	  , [CASHDISCPERCENT]
	  , [CHANGEREQUESTREQUIRED]
	  , [CONFIRMEDDLV]
	  , [CONFIRMEDDLVEARLIEST]
	  , [CONFIRMINGPO_CONFIRMINGPOID]
      , [CONFIRMINGPO_LANGUAGEID]
	  , [CONSTARGET_JP]
	  , [CONTACTPERSONID]
	  , [CONTRACTNUM_SA]
	  , [COUNTYORIGDEST]
	  , [COVSTATUS] 
	  , [CROSSDOCKINGDATE]
      , [CURRENCYCODE]
      , [DEFAULTDIMENSION]
	  , [DEFINITIONGROUP] -- Key 1
      , [DELIVERYADDRESS]
      , [DELIVERYCITY]
      , [DELIVERYCOUNTRYREGIONID]
      , [DELIVERYCOUNTY]
      , [DELIVERYDATE]
	  , [DELIVERYLOCATIONROLE]
	  , [DELIVERYNAME]
	  , [DELIVERYSTATE]
	  , [DELIVERYSTREET]
	  , [DELIVERYTYPE]
      , [DELIVERYZIPCODE]
	  , [DISCPERCENT]
	  , [DLVMODE] 
	  , [DLVTERM]
      , [DOCUMENTSTATE]
	  , [DOCUMENTSTATUS]
	  , [EMAIL]
	  , [ENDDISC] 
	  , [ENTERPRISENUMBER]
	  , [EXCHANGERATEDATE]
	  , [EXECUTIONID]  -- key 2
      , [FINALIZECLOSINGDATE]
      , [FIXEDDUEDATE] 
	  , [FIXEDEXCHRATE]
	  , [FREIGHTSLIPTYPE]
	  , [FREIGHTZONE]
	  , [FSHAUTOCREATED]
	  , [INCLTAX]
	  , [INTERCOMPANYALLOWINDIRECTCREATION]
	  , [INTERCOMPANYCOMPANYID]
	  , [INTERCOMPANYCUSTPURCHORDERFORMNUM]
	  , [INTERCOMPANYDIRECTDELIVERY]
	  , [INTERCOMPANYORDER]
	  , [INTERCOMPANYORIGIN]
	  , [INTERCOMPANYORIGINALCUSTACCOUNT]
	  , [INTERCOMPANYORIGINALSALESID]
	  , [INTERCOMPANYSALESID]
	  , [INTRASTATADDVALUE_LV]
	  , [INTRASTATFULFILLMENTDATE_HU]
	  , [INVENTLOCATIONID]
      , [INVENTSITEID]
      , [INVOICEACCOUNT]
	  , [INVOICEDECLARATIONID_IS]
	  , [INVOICEREGISTER_LT]
	  , [ISENCUMBRANCEREQUIRED]
	  , [ISMODIFIED]
      , [ISSELECTED]
	  , [ITEMBUYERGROUPID]
      , [LANGUAGEID]
	  , [LINEDISC]
	  , [LISTCODE]
	  , [LOCALDELIVERYDATE]
	  , [MARKUPGROUP]
      , [MATCHINGAGREEMENT]
	  , [MULTILINEDISC]
	  , [NUMBERSEQUENCEGROUP]
	  , [ONETIMESUPPLIER]
	  , [ONETIMEVENDOR]
      , [ORDERACCOUNT]
	  , [PACKINGSLIPREGISTER_LT]
	  , [PAYMENT]
	  , [PAYMENTSCHED]
	  , [PAYMMODE]
	  , [PAYMSPEC]
	  , [PORT]
	  , [POSTINGPROFILE]
	  , [PRICEGROUPID]
	  , [PROJID]
	  , [PURCHASETYPE]
      , [PURCHID]      -- key 3
	  , [PURCHNAME]
	  , [PURCHPLACERWORKERPARTYNAME]
	  , [PURCHPLACERWORKERPARTYNUMBER]
	  , [PURCHPLACERWORKERPERSONNELNUMBER]
	  , [PURCHPOOLID]
	  , [PURCHSTATUS]
	  , [REPLENISHMENTLOCATION]
	  , [REQATTENTION]
      , [REQUESTER]
	  , [RETAILCONCESSIONPURCH]
	  , [RETAILDRIVERDETAILS]
      , [RETAILRETAILSTATUSTYPE]
	  , [RETURNITEMNUM]
      , [RETURNREASONCODEID]
      , [RETURNREPLACEMENTCREATED]
	  , [ROWID] 
      , [SEARCHKEY]
	  , [SERVICEADDRESS]
	  , [SERVICECATEGORY]
      , [SERVICEDATE]
      , [SERVICENAME]
	  , [SETTLEVOUCHER]
      , [SKIPUPDATE]
      , [STATPROCID]
      , [SYSTEMENTRYSOURCE]
	  , [TAXGROUP]
	  , [TAXPERIODPAYMENTCODE_PL]
	  , [TRANSACTIONCODE]
	  , [TRANSFERSTATUS]
	  , [TRANSPORT]
      , [TRANSPORTATIONDOCUMENT_CARRIERCODE]
	  , [UNITEDVATINVOICE_LT]
      , [URL]
	  , [VATNUM]
      , [VENDGROUP]
      , [VENDORREF]
      , [RECVERSION]
      , [PARTITION]  -- Key 4
      , [RECID]        
) 
(  SELECT 

	 
			 
	 convert(datetime,podate,120)										    	--- [ACCOUNTINGDATE]	
	,' '						       											--  [ACCOUNTINGDISTRIBUTIONTEMPLATE] 
	, ' '																	    --  [AUTOSUMMARYMODULETYPE]
	,CONVERT(datetime,Podate,120)								     			--  [AVAILSALESDATE]  	--, PODTCR

	
    ,' '																	    --  [BANKCENTRALBANKPURPOSECODE]
    ,' '																	    --  [BANKCENTRALBANKPURPOSETEXT]
    ,' '																	    --  [BANKDOCUMENTTYPE]
    ,' '																	    --  [CASHDISC]
    ,0																		    --  [CASHDISCPERCENT]
    ,' '																	    --  [CHANGEREQUESTREQUIRED]	
    ,convert(datetime,'19010101',120)										    --  [CONFIRMEDDLV]
    ,convert(datetime,'19010101',120)										    --  [CONFIRMEDDLVEARLIEST]
    ,' '																	    --  [CONFIRMINGPO_CONFIRMINGPOID]
	,'US-EN'																    --	[CONFIRMINGPO_LANGUAGEID]
    ,' '																	    --  [CONSTARGET_JP]
    ,' '																	    --  [CONTACTPERSONID]				
    ,' '																	    --  [CONTRACTNUM_SA]
    ,' '																	    --  [COUNTYORIGDEST] 
	, 1																		    --  [COVSTATUS]	
    ,convert(datetime,'19010101',120)										    --  [CROSSDOCKINGDATE]          
	,isnull([POCURR],' ')													    --  [CURRENCYCODE]      
	, '310205--029--'														    --- [DEFAULTDIMENSION]
    ,' '																	    --  [DEFINITIONGROUP] -- key 1
    , RTRIM(LTRIM((VMADR1 + VMADR2)))											--- [DELIVERYADDRESS]
    , ISNULL(SUBSTRING(VMADR3, 1, CHARINDEX(' ',REVERSE(VMADR3), 16)), ' ')     --- [DELIVERYCITY]
    , VMCTRY																	--  [DELIVERYCOUNTRYREGIONID]
    , VMCOUN																	--  [DELIVERYCOUNTY]		
    ,[PECSDT]				    							                    --  [DELIVERYDATE]
    ,' '																	    --  [DELIVERYLOCATIONROLE]
    ,VMCNTC																	    --  [DELIVERYNAME]
	, right(ISNULL(Rtrim(ltrim(VMADR3)), ' '),2)							    --- [DELIVERYSTATE]
	, RTRIM(LTRIM(VMADR1 + VMADR2))											    --- [DELIVERYSTREET]
	, substring(VMADR2,1,10)												    --- [DELIVERYTYPE]
	, VMPZIP																    --- [DELIVERYZIPCODE]
    , 0																		    --  [DISCPERCENT]
    ,' '																	    --- [DLVMODE]
    ,' '																	    --  [DLVTERM]
    , 'Confirmed'															    --- [DOCUMENTSTATE]
    , 'Open Order'															    --- [DOCUMENTSTATUS]
    ,' '							                   							--  [EMAIL]	
    ,' '																	    --  [ENDDISC]
    ,' '																	    --  [ENTERPRISENUMBER]
    , convert(datetime,[PECSDT],120)									    	--  [EXCHANGERATEDATE]
    ,' '																	    --  [EXECUTIONID] -- key 2
    , convert(datetime,[PECSDT],120)										    --- [FINALIZECLOSINGDATE]
    , convert(datetime,[PECSDT],120)										    --- [FIXEDDUEDATE]
    ,0																		    --  [FIXEDEXCHRATE]
    ,' '																	    --  [FREIGHTSLIPTYPE]
    ,' '																	    --  [FREIGHTZONE]
    ,' '																	    --  [FSHAUTOCREATED]	
    ,' '																	    --  [INCLTAX]
    ,' '																	    --  [INTERCOMPANYALLOWINDIRECTCREATION] 
    ,' '																	    --  [INTERCOMPANYCOMPANYID]
    ,' '																	    --  [INTERCOMPANYCUSTPURCHORDERFORMNUM]
    ,' '																	    --  [INTERCOMPANYDIRECTDELIVERY]
    ,' '																     	--  [INTERCOMPANYORDER]
    ,' '						a											    --  [INTERCOMPANYORIGIN]
    ,' '																	    --  [INTERCOMPANYORIGINALCUSTACCOUNT]
    ,' '																	    --  [INTERCOMPANYORIGINALSALESID]
    ,' '																	    --  [INTERCOMPANYSALESID]
    ,0																		    --  [INTRASTATADDVALUE_LV]
    , convert(datetime,'19010101',120)										    --  [INTRASTATFULFILLMENTDATE_HU]
	 ,ISNULL((select														    --  [INVENTLOCATIONID]
	    CASE
		WHEN isnumeric(b.pono) = 0 or isnumeric(ovpono) = 0 then '01' 
 		WHEN substring(ovadd1,1,4) = '' then '01'																 
		WHEN substring(ovadd1,1,4) = '2720' then '01'
		WHEN substring(ovadd1,1,4) = '8506' then '02'
		WHEN b.pono = null then '01'
 		END 
		from [FLSHTOOV] 
		   WHERE B.[POCO] = OVCO and B.[PONO] = OVPONO
		       and ovname  > ' ' ),'01')

 
    , '01'																	     --- [INVENTSITEID]
    , [POVEND]																     ----[INVOICEACCOUNT]
    ,' '																	     --  [INVOICEDECLARATIONID_IS]
    ,' '																	     --  [INVOICEREGISTER_LT]
    ,' '																	     --  [ISENCUMBRANCEREQUIRED]
    ,' '																	     --  [ISMODIFIED]
    , 1																		     ----[ISSELECTED]
    ,' '																	     --  [ITEMBUYERGROUPID]
    , 'US_EN'																     ----[LANGUAGEID]
    ,' '																	     --  [LINEDISC]
    ,' '																	     --  [LISTCODE]
    , convert(datetime,'19010101',120)										     --  [LOCALDELIVERYDATE]
    ,' '																	     --  [MARKUPGROUP]
    , 0																		     ----[MATCHINGAGREEMENT]
    ,' '																	     --  [MULTILINEDISC]
    ,' '																	     --  [NUMBERSEQUENCEGROUP]
    ,' '																	     --  [ONETIMESUPPLIER]
    ,' '																	     --  [ONETIMEVENDOR]
    ,' '																	     ----[ORDERACCOUNT]  --, [POVEND] 
    ,' '																	     --  [PACKINGSLIPREGISTER_LT]
    ,' '																	     --  [PAYMENT]
    ,' '																	     --  [PAYMENTSCHED]
    ,' '																	     --  [PAYMMODE]
    ,' '																	     --  [PAYMSPEC]
    ,' '																	     --  [PORT]
    ,' '																	     --  [POSTINGPROFILE]
    ,' '																	     --  [PRICEGROUPID]
    ,' '																	     --  [PROJID]
    ,' '																	     --  [PURCHASETYPE] 
    , [PONO]																     --	[PURCHID]  -- key 3
    ,' '																		 --  [PURCHNAME]
    ,' '																	     --  [PURCHPLACERWORKERPARTYNAME]
    ,' '																	     --  [PURCHPLACERWORKERPARTYNUMBER]
    ,' '																	     --  [PURCHPLACERWORKERPERSONNELNUMBER]
   ,' '																		     --  [PURCHPOOLID]
   ,' '																		     --- [PURCHSTATUS]
   ,' '																		     --  [REPLENISHMENTLOCATION]
   ,' '																		     --  [REQATTENTION]
    , 1																		     --	[REQUESTER]
    ,' '																	     --  [RETAILCONCESSIONPURCH]
    ,' '																	     --  [RETAILDRIVERDETAILS]
	,' '																	     --  [RETAILRETAILSTATUSTYPE]
    ,' '																	     --  [RETURNITEMNUM]
    ,' '																	     --  [RETURNREASONCODEID]
    ,' '																	     --  [RETURNREPLACEMENTCREATED]
    ,0																		     --  [ROWID]
    , 1																		     --	[SEARCHKEY]
    ,' '																	     --  [SERVICEADDRESS]
    ,' '																	     --  [SERVICECATEGORY]
    , convert(datetime,'19010101',120)										     --	[SERVICEDATE]
    ,' '																	     --  [SERVICENAME]
    ,' '																	     --  [SETTLEVOUCHER]
    ,' '																	     --  [SKIPUPDATE]
    ,' '																	     --  [STATPROCID]
    ,' '																	     --  [SYSTEMENTRYSOURCE]
    ,' '																	     --  [TAXGROUP]
    ,' '																	     --  [TAXPERIODPAYMENTCODE_PL]
    ,' '																	     --  [TRANSACTIONCODE]
    , 0																		     --  [TRANSFERSTATUS]
    ,' '																	     --  [TRANSPORT]
    ,' '																	     --  [TRANSPORTATIONDOCUMENT_CARRIERCODE]
    ,' '																	     --  [UNITEDVATINVOICE_LT]
    ,' '																	     --  [URL]
    ,' '																	     --  [VATNUM]
    ,' '																	     --  [VENDGROUP]
    ,' '																	     --  [VENDORREF]
    ,0																		     --	[RECVERSION]
    ,0																		     --  [PARTITION]  -- Key 4
    , 1																		     --  [RECID]

			 
FROM  [FLPOSUM] b 
	inner join  [FLVENDM] c on c.[VMCO] = b.[POCO] and c.[VMNO] = b.[POVEND]
	inner join  [FLPODET] d on d.[PECO] = b.[POCO] and d.[PENO] = b.[PONO]  and d.[PELINE] = b.[POLINE]  
    inner join [DMFPRODUCTENTITY] e on e.[ITEMID] = b.[POPN] 
            WHERE EXISTS (SELECT * FROM [DMFPRODUCTENTITY] 
			                WHERE  e.[ITEMID] = B.[POPN])
								and 
			   NOT EXISTS (SELECT * FROM [DMFPURCHTABLEENTITY] f	
			                WHERE  b.[PONO] = f.[PURCHID]  )
							and b.POCO in (1,3,5,8)	
  							and b.POSTS = 'O'
							and b.POVEND < 9999900

Open in new window

LeogalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil RussellTechnical Development LeadCommented:
Firstly why are you trying to convert FROM a STRING, TO a DATE and then store the result in a DECIMAL(6, 0) ?
0
lcohanDatabase AnalystCommented:
select CAST( '120307' as date)
0
LeogalAuthor Commented:
@neilsr the data comes in as a decimal(6,0). Then it has to be converted to final destination as a date.
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

LeogalAuthor Commented:
@lchohan when I try select cast (podate as date) i receive this error:

Msg 529, Level 16, State 2, Line 147
Explicit conversion from data type decimal to date is not allowed.
0
Neil RussellTechnical Development LeadCommented:
No that would be expected.  Why on earth do you have a date stored as a decimal number?
You will need to convert the decimal to a string of fixed length and then convert the string to the date.
0
Scott PletcherSenior DBACommented:
The date isn't in format 120, which is yyyy-mm-dd.

It's not in any standard format, so we need to adjust its format slightly.  The closest format code is 12, which is 'yymmdd', so we need to adjust PODATE that into format:

CONVERT(datetime, RIGHT(PODATE, 2) + LEFT(PODATE, 4), 12)
0
lcohanDatabase AnalystCommented:
select cast (cast(podate as sysname) as date)
0
Haris DulicCommented:
maybe a little long solution but gives the requested format...

select cast (('20'+substring(cast(podate   as char(6)),5,2) + '-' + substring(CAST(podate AS CHAR(6)),1,2) + '-'+substring(CAST(podate AS CHAR(6)),3,2)) as datetime)

Open in new window


here you can test it
0
Scott PletcherSenior DBACommented:
@samo4fun:
If you're going to do it that way, get rid of the dashes, because they just add chances for error.  Format 'YYYYMMDD' is always interpreted correctly when being converted to a date.

select '20' + right(podate, 2) + left(podate, 4)
0
LeogalAuthor Commented:
@scottplecher when I try it your way I get this error:
(0 row(s) affected)
Msg 242, Level 16, State 3, Line 7
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

@lochan when I try it your way I get this error
(0 row(s) affected)
Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.

@samo4fun I tryed it your way and got this error:

(0 row(s) affected)
Msg 242, Level 16, State 3, Line 7
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
0
Haris DulicCommented:
Not clear way you got this error, since here i got the right result ..

http://sqlfiddle.com/#!6/d41d8/22415
0
Scott PletcherSenior DBACommented:
Then you have bad in your input.

For example:
121307
120332

I assumed the format was YYMMDD, since you were using code 120.

If it's YYDDMM, then naturally we need to reformat it slightly differently:


CONVERT(datetime, RIGHT(PODATE, 2) + SUBSTRING(PODATE, 3, 2) + LEFT(PODATE, 2), 12)
0
Haris DulicCommented:
I guess it is MMDDYY
0
Scott PletcherSenior DBACommented:
CORRECTION:

I assumed MMDDYY.  If it's DDMMYY, then a different conversion is required.

I reformatted MMDDYY to YYMMDD, which should always work with an explicit format of 12 (for 'YYYYMMDD', you don't need to use a format at all).
0
LeogalAuthor Commented:
@scottplecher when I do this
convert(datetime,20 + right(podate, 2) + left(podate, 4),111)       there is no error, however the date is wrong
instead of 2007-12-03  the result is 1903-05-16 00:00:00.000
0
Scott PletcherSenior DBACommented:
convert(datetime,20 + right(podate, 2) + left(podate, 4),111)
Woah, where did format "111" come from?  It's 112 if you want to add the '20'/century yourself.  The format numbers are not arbitrary, they must match the specific pattern of the input data.
And you can't mix strings and numbers, only strings, so '20', not 20.


Also, for 2-digit only years, you have to consider the option setting on SQL for when the year is assumed to switch.

You can change the:
two digit year cutoff Option

if you want to always use 2000 instead of 1900 when SQL reads a 2-digit year:

EXEC sp_configure 'two digit year cutoff Option', 2000
RECONFIGURE
0
LeogalAuthor Commented:
@scottplecher when I do this
The date isn't in format 120, which is yyyy-mm-dd.

It's not in any standard format, so we need to adjust its format slightly.  The closest format code is 12, which is 'yymmdd', so we need to adjust PODATE that into format:

CONVERT(datetime, RIGHT(PODATE, 2) + LEFT(PODATE, 4), 12)


It works on dates that have all 6 characters, ie, mmddyy, however for those months that are single digit mddyy,  i get this error:


(2 row(s) affected)
Msg 242, Level 16, State 3, Line 7
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
0
Scott PletcherSenior DBACommented:
Ooh, good point, I overlooked that.


CONVERT(datetime, RIGHT(RIGHT('0' + CAST(PODATE AS varchar(6)), 6), 2) + LEFT(RIGHT('0' + CAST(PODATE AS varchar(6)), 6), 4), 12)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeogalAuthor Commented:
@scottplecher is awesome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2012

From novice to tech pro — start learning today.