Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: M. Akram Bhatti
  Where is M. Akram Bhatti?
 Riyadh
 Saudi Arabia
 M. Akram Bhatti
 To: pat mazuel
  Where is pat mazuel?
 riom
 France
 pat mazuel
 Tags
Subject: RE: calculating datetime
Thread ID: 249114 Message ID: 249127 # Views: 2 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Tuesday, December 29, 2009 6:20:20 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

Hi Pat,

Although your modified code is working fine (as u claimed) but after getting the result into a writable cursor, you did not set any relation to the Vitals Table instead directly replacing all the records with values reading from Vitals Table.

What will happen with out relation all Date_Prise values will be replaced with the values of the Vitals Table's record where the current pointer is.

Try the below code (not tested)

SELECT patient.code, vitals.codepat , ;
IIF(NOT EMPTY(vitals.dateprise), ;
DATETIME(YEAR(vitals.dateprise), month(vitals.dateprise), DAY(vitals.dateprise), vitals.heureprise,0,0), ;
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


-added later:

CREATE CURSOR myCur (DatePrise d, HeurePrise i)
INSERT INTO myCur VALUES (DATE()-1, 1)
INSERT INTO myCur VALUES ({}, 0)
INSERT INTO myCur VALUES (DATE()+1, 2)

* Error in the this Query
SELECT DATETIME(YEAR(dateprise), month(dateprise), DAY(dateprise), heureprise,0,0) AS Date_Prise ;
FROM myCur

* No Error works fine (Empty Date)
SELECT IIF(NOT EMPTY(dateprise), DATETIME(YEAR(dateprise), month(dateprise), DAY(dateprise), heureprise,0,0), ;
CTOT("  /  /    ")) AS Date_Prise ;
FROM myCur

* No Error works fine (CurrentDate)
SELECT IIF(NOT EMPTY(dateprise), DATETIME(YEAR(dateprise), month(dateprise), DAY(dateprise), heureprise,0,0), ;
DATETIME()) AS Date_Prise ;
FROM myCur


Regards,
akram

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