Hi All,
Can i have the example sql statement about the DATEDIFF(exp,exp)?
i tried the follwowing command. but it is not worked.
select count(*) from scanAnalysis a where TRIM(a.patient_key) = ? and
date(a.scan_date)= date(?) or datediff(date(a.scan_date),date(?))<=5 and a.scan_type IN (6,7);
a.patient_key='1JTC04146R0883050'
date(?) =12/4/2005.
what is wrong in this query, Syntax error in expression getting.
Could you please provide mre the the sample query.
Thnaks,
Krishna
|
If you're using PreparedStatement, you can use your sql, or datediff(date(a.scan_date),?)<=5 . You shouln't meet syntax error.
>date(?) =12/4/2005.
In sql, you need ' to enclose date string. For instance '2005-12-04'.
select date('12/4/2005');
will be detected and converted into select date('2005-12-04') .
|
Hi,
i tried this sql statement. it is working and giving the result.
SELECT a.patient_key, a.scan_date, datediff("d",a.scan_date,6/28/2010)>=5 FROM ScanAnalysis a where a.patient_key in ('1JTC04146R0883050','1JU40912IO0583050');
but if i give this statement , it is not working.
SELECT a.patient_key, a.scan_date, datediff(date(a.scan_date),6/28/2010)<=5 FROM ScanAnalysis a where a.patient_key in('1JTC04146R0883050','1JU40912IO0583050');
or
SELECT a.patient_key, a.scan_date, datediff("d",date(a.scan_date),6/28/2010)>=5 FROM ScanAnalysis a where a.patient_key in ('1JTC04146R0883050','1JU40912IO0583050');
What could be the reason?
Thanks,
Sai
|
1.st 6/28/2010 will become 1.0660980810234541E-4 value, not a date value:(
You should use '6/28/2010' or '2010-06-28' or {d '2010-06-28'}.
2nd. DATEDIFF supports 2~3 parameter, but it means different format.
2 parameters will calculate only days.
DATEDIFF(expr,expr2): returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
3 parameters can calculate any part of date.
DATEADD(INTERVAL, expr, date), DATEDIFF (INTERVAL, date1, date2), DATEPART (INTERVAL, date) For instance, SELECT DateAdd('m', 3, date1) FROM test;
|
Main
webmaster@hxtt.com