Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 16th December 2016, 22:34
eric.dizon's Avatar
eric.dizon eric.dizon is offline
Senior Member
 
Join Date: Feb 2012
Posts: 151
eric.dizon is on a distinguished road
Baan: ERP LN - DB: MS SQL - OS: 2003
Baan SQL Group by datetime but ignore minute and hour
Baan: ERP LN 6.1 FP7 (Infor ERP 10.1)
C/S: None/Unknown

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
Reply With Quote
  #2  
Old 18th December 2016, 06:27
pillai.ganesh's Avatar
pillai.ganesh pillai.ganesh is offline
Senior Member
 
Join Date: Apr 2011
Location: United Arab Emirates
Posts: 105
pillai.ganesh is on a distinguished road
Baan: ERP LN 6.1 - DB: Oracle, SQL, Informix - OS: Windows
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.
Reply With Quote
  #3  
Old 18th December 2016, 11:02
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,639
bdittmar will become famous soon enough
Baan: 2.2/3.1/4c4/LN6.1 FP6/FP9/HiDox - DB: tbase, ms-sql7, oracle10gV1, 11g - OS: HP-UX, W2K3, SLES
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
__________________
//Bernd
Reply With Quote
  #4  
Old 19th December 2016, 20:48
eric.dizon's Avatar
eric.dizon eric.dizon is offline
Senior Member
 
Join Date: Feb 2012
Posts: 151
eric.dizon is on a distinguished road
Baan: ERP LN - DB: MS SQL - OS: 2003
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)
Reply With Quote
  #5  
Old 20th December 2016, 02:09
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
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
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
Sponsored Links
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Baan World Users conference in Orlando chadwickh General Discussion & Chat 7 3rd November 2001 21:55


All times are GMT +2. The time now is 12:06.


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