Date, Time or Timestamp value is not valid.

[kenny99076]

Hey everyone, this is my first post here so go easy on me haha.

Basically in my program I'm taking the current date, then with that current date I want to use the first of that month. Then I want to get the difference in months between the date I built and another date.

Here's my d-Spec:

D TDATE_           S             10D   DATFMT(*ISO)INZ(*SYS) 
D TDiffMonths_      S              4S 0                       
D CompDate_        S             10d                         
D TDateDay_        S              2A                         
D TDateYear_       S              4A                         
D TDateMonth_      S              2A                         
D HoldField_       S             10A  
D paydate_                        d   datfmt(*eur)    

and here's my code:


/free

  TDateYear_ = %Subst(%Char(TDATE_):1:4);                           
  TDateMonth_ = %Subst(%Char(TDATE_):6:2);                          
  TDateDay_ = '01';                                                 
  HoldField_ = TDateDay_ + '/' + TDateMonth_ + '/' + TDateYear_;    
  CompDate_ = %DATE(HoldField_:*ISO);                [B]//my error happens here   [/B]                   
  DiffMonths_ = %Diff( paydate_: CompDate_: *MONTHS ); //paydate_ gets populated before all this code.

/end-free

Date, Time or Timestamp value is not valid.

Any help you guys could give would be awesome. Been working on this for a few days, and I've only been programming for a year or so. I need some of your amazing wisdom!!!

[tomholden]

Re: Date, Time or Timestamp value is not valid.

Hey everyone, this is my first post here so go easy on me haha.

Basically in my program I'm taking the current date, then with that current date I want to use the first of that month. Then I want to get the difference in months between the date I built and another date.

Here's my d-Spec:

D TDATE_           S             10D   DATFMT(*ISO)INZ(*SYS) 
D TDiffMonths_      S              4S 0                       
D CompDate_        S             10d                         
D TDateDay_        S              2A                         
D TDateYear_       S              4A                         
D TDateMonth_      S              2A                         
D HoldField_       S             10A  
D paydate_                        d   datfmt(*eur)    

and here's my code:


/free

  TDateYear_ = %Subst(%Char(TDATE_):1:4);                           
  TDateMonth_ = %Subst(%Char(TDATE_):6:2);                          
  TDateDay_ = '01';                                                 
  HoldField_ = TDateDay_ + '/' + TDateMonth_ + '/' + TDateYear_;    
  CompDate_ = %DATE(HoldField_:*ISO);                [B]//my error happens here   [/B]                   
  DiffMonths_ = %Diff( paydate_: CompDate_: *MONTHS ); //paydate_ gets populated before all this code.

/end-free

Date, Time or Timestamp value is not valid.

Any help you guys could give would be awesome. Been working on this for a few days, and I've only been programming for a year or so. I need some of your amazing wisdom!!!

the date you are putting in HoldField isn't an ISO date. the date you are constructing would look like this DD/MM/YYYY which (to my knowledge isn't a valid date construct regardless of format. the closest would be *EUR but periods are used for separators) for an ISO date you should be using something like this:

HoldField = TDateYear_ + '-' + TDateMonth + '-' + TDateDay_;

since you haven't specified a date format for CompDate_ *ISO is assumed (unless you have specified a different format on an H spec)

ETA: please use [code][/code] tags around your code so it's more readable

[kenny99076]

Re: Date, Time or Timestamp value is not valid.

Thanks, I'll have to try that. I'll let you know how it turns out. Also, thanks for the tags. I will remember for next time. I think I'll be posting here a lot lol.

[MichaelCatalani]

Re: Date, Time or Timestamp value is not valid.

d TodayISO        s               d   DatFmt( *ISO ) Inz                                           
d PayDateEuro     s               d   DatFmt( *EUR ) Inz                                           
d PayDateISO      s               d   DatFmt( *ISO ) Inz                                           
d DiffMonths      s             10i 0                                                              
                                                                                                   
 /free                                                                                             
                                                                                                   
   // We'll set an ISO date field to today                                                         
   TodayISO = %date();                                                                             
                                                                                                   
   // Move a random date in ISO format into the PayDateISO field                                   
   PayDateISO = %date( '2011-06-25' : *ISO );                                                      
                                                                                                   
   // Once we have a valid date in a valid date format, we can easily interchange                  
   // date formats with the "=" operand.                                                           
   PayDateEuro  = PayDateISO;                                                                      
                                                                                                   
   // The DIFF bif can handle different date formats                                               
   // Use the %ABS BIF on the DIFF bif if you always want the difference to be a positive number   
   DiffMonths = %abs( %Diff( PayDateEuro : TodayISO : *Months ));                                  
                                                                                                   
   dsply %char( DiffMonths );                                                                      
   *inlr = *on;

[B.Hauser]

Re: Date, Time or Timestamp value is not valid.

A date format with a 4 digit year has predefined separators, i.e.:
*USA --> Slash / --> Format: MM/DD/YYYY
*ISO --> Hyphen - --> Format: YYYY-MM-DD
*EUR --> Dot . --> Format: DD.MM.YYYY

Instead of using any date separator I'd abandon them:

/Free
   HoldDate = TDateYear_ + TDateMonth_ + '01';
   CompDate = %Date(HoldDate: *ISO[B]0[/B]);
/End-Free

Birgitta

[kenny99076]

Re: Date, Time or Timestamp value is not valid.

So I'm still having trouble getting this to work. I tried changing the separators to '-' and I tried taking them out all together, but I'm still getting that error.

I noticed in my H-spec I have this:

h Option(*NoDebugIO) Aut(*All) Indent('| ') DatEdit(*YMD)

Any ideas?

[kenny99076]

Re: Date, Time or Timestamp value is not valid.

Ok nevermind!! I got it figured out. Turns out when I made the changes you guys suggested I switched some fields around lol. After looking at it for so long my mind went a blur. Thanks so much for your help everyone!