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: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: calculating datetime
Thread ID: 249114 Message ID: 249120 # Views: 2 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Monday, December 28, 2009 9:37:34 PM         
   


> > 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
>
> DATE()-15 doesn't necessarily give a date in the same month, only in 50% of the cases.
>
> -Anders


I know , date()-15 should give me a date that is 15 days before the current date. What kept me from seeing the empty datefield is that when the resulting date fell within the same month, my select worked, when it fell in the previous month, the select failed. Still not sure why that is, but the empty date was causing the problem.

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