Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Yuri Rubinov
  Where is Yuri Rubinov?
 Philadelphia
 Pennsylvania - United States
 Yuri Rubinov
 To: pat mazuel
  Where is pat mazuel?
 riom
 France
 pat mazuel
 Tags
Subject: RE: calculating datetime
Thread ID: 249114 Message ID: 249115 # Views: 4 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Monday, December 28, 2009 7:13:10 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

Hi,

Probably there is a conflict with date setting, and date as it is saved in the record. How are dates presented in the table, and what is set("date")?

Other than that, to "understand why the first select causes an error", first you need to read the error message. It should clearly indicate what the problem is, and, probably, give you a hint of where in your statement it happened. If there is no hint, then you need to work with your statement making it less complicated while, say, removing some pieces, and to get exactly the cause of the problem.

When you get the problem description, and the place in your code, and you are not able to figure out why it behaves this way, then provide here all the details.


Best Wishes,

Yuri Rubinov

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