Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Stefan Wuebbe
  Where is Stefan Wuebbe?
 Hamburg
 Germany
 Stefan Wuebbe
 To: pat mazuel
  Where is pat mazuel?
 riom
 France
 pat mazuel
 Tags
Subject: RE: calculating datetime
Thread ID: 249114 Message ID: 249116 # Views: 2 # Ratings: 1
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Monday, December 28, 2009 8:11:37 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



> 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