Baanboard.com

Baanboard.com (http://www.baanboard.com/baanboard/index.php)
-   Tools Development (http://www.baanboard.com/baanboard/forumdisplay.php?f=8)
-   -   Baan SQL Group by datetime but ignore minute and hour (http://www.baanboard.com/baanboard/showthread.php?t=69551)

eric.dizon 16th December 2016 23:34

Baan SQL Group by datetime but ignore minute and hour
 
Hi,

I am trying to find out how to get the unique days in a table using Baan SQL, I can do it in T-SQL by ignoring hour/minute and just extracting the date in a like '2016/12/16' format.
T-SQL
select
convert(varchar, d.log_date, 101) as log_date,
group by
convert(varchar, d.log_date, 101)


Can please somebody point me to the right direction?

Regards,
Eric

pillai.ganesh 18th December 2016 07:27

CAST function is available in LN Tools for the purpose.

See the below example-
domain tfgld.date h.date
select CAST(tdsls400.odat AS DATE):h.date
from tdsls400
selectdo
<some code>
endselect

But then because you cannot use CAST function in GROUP BY Clause in LN... You may have to handle the grouping inside the selectdo I guess.

bdittmar 18th December 2016 12:02

Cast
 
Hello,

Code:

CAST expression
With the CAST expression you can assign a type to a parameter.

Syntax

<cast expression>
    ::= CAST ( Parameter AS <parameter cast type> )

<parameter cast type>
    ::= INTEGER | REAL | DATE | TIMESTAMP | STRING | RAW
Semantics

The cast operator assigns the type <parameter cast type> to the <parameter>. The cast expression itself will also be of type <parameter cast type>.

Examples

The following CAST expression assigns the type integer to the parameter param.

CAST ( :param AS INTEGER )
The following CAST expression assigns the type raw to the parameter param.

CAST ( :param AS RAW )
Resolving type conflicts on parameters

The cast operator is used to properly type parameters in case of ambiguities or in case of possible type conflicts.

In the following example, both param1 and param2 cannot be typed, because each type is comparable to itself.

:param1 = :param2
This problem can be resolved using the CAST expression.

:param1 = CAST( :param2 AS STRING )
In the following example the first comparison types param as date, while the second comparison types it as real.

:param = hiredate  or  :param = 0
Using the CAST expression this can be resolved.

:param = hiredate  or  CAST( :param AS DATE ) = 0

Regards

eric.dizon 19th December 2016 21:48

Thanks for the response but I still cannot figure out how to get the exact same grouping of dates that I can do in T-SQL.

SELECT CONVERT(VARCHAR,t_ldat,101) FROM tcxcsb105600
GROUP BY CONVERT(VARCHAR,t_ldat,101)

mark_h 20th December 2016 03:09

From searching the forum it appears people have been asking this question for years with no simple solution. While it appears to be clunky maybe something like this would work. The problem is if you have a large record set with only a few dates you skip a lot of records doing nothing.
Code:


domain tfgld.date h.date
on.change.check(h.date)
 select CAST(tdsls400.odat AS DATE):h.date
 from tdsls400
 selectdo
  if changed(h.date) then
    |Got a new date so do something with it.
  endif
endselect



All times are GMT +2. The time now is 11:28.


vB.Sponsors
©2001-2017 - Baanboard.com - Baanforums.com