Solved

date issues using convert in TSQL

Posted on 2014-10-21
19
192 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
ID: 40395365
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
ID: 40395367
select CAST( '120307' as date)
0
 

Author Comment

by:Leogal
ID: 40395381
@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
ID: 40395386
@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
ID: 40395396
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
ID: 40395402
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
ID: 40395406
select cast (cast(podate as sysname) as date)
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40395425
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
ID: 40395446
@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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Leogal
ID: 40395453
@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
ID: 40395474
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
ID: 40395486
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
ID: 40395495
I guess it is MMDDYY
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40395508
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
ID: 40395509
@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
ID: 40395541
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
ID: 40395629
@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
ID: 40395677
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
ID: 40395976
@scottplecher is awesome!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
OfficeMate Freezes on login or does not load after login credentials are input.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…

912 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

22 Experts available now in Live!

Get 1:1 Help Now