Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: tushar Kanvinde
  Where is tushar Kanvinde?
 Kolhapur, Maharastra
 India
 tushar Kanvinde
 To: pat mazuel
  Where is pat mazuel?
 riom
 France
 pat mazuel
 Tags
Subject: RE: calculating datetime
Thread ID: 249114 Message ID: 249128 # Views: 2 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Tuesday, December 29, 2009 6:40:41 AM         
   


> 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

This is not the answer to your query but shouldn't

REPLACE date_prise WITH DATETIME(YEAR(vitals.dateprise), month(vitals.dateprise), DAY(vitals.dateprise), vitals.heureprise,0,0) all


be

REPLACE date_prise WITH DATETIME(YEAR(dateprise), month(dateprise), DAY(dateprise), heureprise,0,0) all


Check vitals.dateprise for empty date field.

Regards
Tushar

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