Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: pat mazuel
  Where is pat mazuel?
 riom
 France
 pat mazuel
 To: Stefan Wuebbe
  Where is Stefan Wuebbe?
 Hamburg
 Germany
 Stefan Wuebbe
 Tags
Subject: RE: calculating datetime
Thread ID: 249114 Message ID: 249117 # Views: 2 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Monday, December 28, 2009 8:31:02 PM         
   


> Salut Pat,
>
> > While I now have my code working, I'd really like to understand why the first select causes an error.
> >
>
> What error message/number did you get?
> As a guess: if you are "concatenating" a datetime value from a date + an integer using the DateTime() function, success would depend on valid values, i.e. an empty date portion would probably give an "Function argument value, type, or count is invalid (Error 11)".
>
> So that in an SQL statement, Cast() might work better by being able to handle empty and null values.
>
>
> * Test Data:
> CREATE CURSOR vitals (dateprise D NULL, heureprise I NULL)
> INSERT INTO vitals VALUES (DATE(), 15)
> 
> * Your first approach works on valid values:
> SELECT DATETIME(YEAR(vitals.dateprise), month(vitals.dateprise), DAY(vitals.dateprise), vitals.heureprise,0,0) as date_prise FROM vitals
> 
> * ... but fails if there are "invalid" rows in the SQL result:
> INSERT INTO vitals VALUES ({}, 0)
> SELECT DATETIME(YEAR(vitals.dateprise), month(vitals.dateprise), DAY(vitals.dateprise), vitals.heureprise,0,0) as date_prise FROM vitals
> 
> * Cast() works anyway:
> SELECT CAST(dateprise as T) + heureprise *60*60 FROM vitals
> 
> INSERT INTO vitals VALUES (.Null., .Null.)
> SELECT CAST(dateprise as T) + heureprise *60*60 FROM vitals
> 

>
>
>
> hth
> -Stefan
>
Thanks to both of you.

Stehan the error message was "Function argument value, type, or count is invalid (Error 11)",
Sorry Yuri that I didn't think to include it.

There was one "dateprise" that was empty. As soon as I filled it in all worked correctly.
I'm not sure why it was working if I only took the last 15 days, but failed if I took the last 30 days, but it did.
But at least now I know what the problem was.

Stefan, I can't find any references to a CAST function in VFP ?

Pat



> > Hi,
> > I have a date field called dateprise, and a numeric field called heureprise. Heureprise can be any integer value >=0 and <=23
> >
> > The first bloc of code works providing that the date is in the second half of the month. ie date()-15 gives a date in the same month.
> > so if I execute it today, I get dec 28, 2009 - 15 = dec 13
> > If I execute it on dec 5, 2009 I get an error message
> >
> >
> >
SELECT patient.code, vitals.codepat , DATETIME(YEAR(vitals.dateprise), month(vitals.dateprise), DAY(vitals.dateprise), vitals.heureprise,0,0) as date_prise , ;
> > 	 vitals.poids,	patient.taille , patient.datesort  ;
> >  	FROM vitals left join patient on patient.code=vitals.codepat ;
> >  	where EMPTY(datesort) OR (!EMPTY(datesort) AND datesort >= (DATE()-15 ) ) ;
> >   	order by patient.code, dateprise desc , heureprise ASC INTO CURSOR cur_imc readwrite
> > 

> >
> > I've modified my code to get the dateprise, and heureprise in the select while adding a datetime field called date_prise. I then calculate the value of date_prise and all works perfect. I can't understand why the datetime fonction would cause an error message by the selecting of a date in the where clause
> >
> >
SELECT patient.code, vitals.codepat , vitals.dateprise , heureprise  , datetime() as date_prise, ;
> > 	 vitals.poids,	patient.taille , patient.datesort  ;
> >  	FROM vitals left join patient on patient.code=vitals.codepat ;
> >  	where EMPTY(datesort) OR (!EMPTY(datesort) AND datesort >= (DATE()-15 ) ) ;
> >   	order by patient.code, dateprise desc , heureprise ASC INTO CURSOR cur_imc readwrite
> > 
> > SELECT cur_imc
> > REPLACE date_prise WITH DATETIME(YEAR(vitals.dateprise), month(vitals.dateprise), DAY(vitals.dateprise), vitals.heureprise,0,0) all

> >
> > While I now have my code working, I'd really like to understand why the first select causes an error.
> >
> > TIA
> > Pat

ENTIRE THREAD

calculating datetime Posted by pat mazuel @ 12/28/2009 6:37:32 PM
RE: calculating datetime Posted by Yuri Rubinov @ 12/28/2009 7:13:10 PM
RE: calculating datetime Posted by Stefan Wuebbe @ 12/28/2009 8:11:37 PM
RE: calculating datetime Posted by pat mazuel @ 12/28/2009 8:31:02 PM
RE: calculating datetime Posted by Anders Altberg @ 12/28/2009 9:33:15 PM
RE: calculating datetime Posted by pat mazuel @ 12/28/2009 9:43:11 PM
RE: calculating datetime Posted by Anders Altberg @ 12/29/2009 12:37:32 AM
RE: calculating datetime Posted by Anders Altberg @ 12/28/2009 9:29:15 PM
RE: calculating datetime Posted by pat mazuel @ 12/28/2009 9:37:34 PM
RE: calculating datetime Posted by M. Akram Bhatti @ 12/29/2009 6:20:20 AM
RE: calculating datetime Posted by tushar @ 12/29/2009 6:40:41 AM
RE: calculating datetime Posted by pat mazuel @ 12/29/2009 7:58:35 AM