Solved

date issues using convert in TSQL

Posted on 2014-10-21
19
183 Views
Last Modified: 2014-10-21
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

0
Comment
Question by:Leogal
  • 6
  • 6
  • 3
  • +2
19 Comments
 
LVL 37

Expert Comment

by:Neil Russell
Comment Utility
Firstly why are you trying to convert FROM a STRING, TO a DATE and then store the result in a DECIMAL(6, 0) ?
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
select CAST( '120307' as date)
0
 

Author Comment

by:Leogal
Comment Utility
@neilsr the data comes in as a decimal(6,0). Then it has to be converted to final destination as a date.
0
 

Author Comment

by:Leogal
Comment Utility
@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
 
LVL 37

Expert Comment

by:Neil Russell
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
select cast (cast(podate as sysname) as date)
0
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
@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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Leogal
Comment Utility
@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
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
Not clear way you got this error, since here i got the right result ..

http://sqlfiddle.com/#!6/d41d8/22415
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
I guess it is MMDDYY
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 

Author Comment

by:Leogal
Comment Utility
@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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 

Author Comment

by:Leogal
Comment Utility
@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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:Leogal
Comment Utility
@scottplecher is awesome!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

The reason that corporations and businesses use Windows servers is because it supports custom modifications to adapt to the business and what it needs. Most individual users won’t need such powerful options. Here I’ll explain how you can enable Wind…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 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

12 Experts available now in Live!

Get 1:1 Help Now