Baanboard.com

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

User login

Frontpage Sponsor

Main

Google search


Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
18%
200 - 500 GB
26%
500 - 800 GB
3%
800 - 1200 GB
9%
1200 - 1500 GB
12%
1500 - 2000 GB
12%
> 2000 GB
21%
Total votes: 34

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 24th April 2015, 22:42
Joy Conner's Avatar
Joy Conner Joy Conner is offline
Guru
 
Join Date: Aug 2001
Posts: 375
Joy Conner is on a distinguished road
Baan: IVc4 - DB: MS SQL - OS: Windows Server 2008 & 2008 R2
null date
Baan: Other/Unknown
C/S: None/Unknown

how do you write sql statement and include records where a date field is null?

select table.*
from table
where table.date_field = 0

This used to work when running on unix / informix. But today it does not work on windows / sql. ???
Reply With Quote
  #2  
Old 25th April 2015, 03:04
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,145
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Joy,

If you don't mind could you post the solution so someone else in the future could find a solution for the problem.
__________________
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
  #3  
Old 27th April 2015, 12:59
RedBatz's Avatar
RedBatz RedBatz is offline
Member
 
Join Date: Oct 2005
Location: Lisbon, Portugal
Posts: 61
RedBatz is on a distinguished road
Baan: Baan IV c4 - DB: MSSQL2005 - OS: W2003
Hi,

the sql statement is executed directly in the database?

If so, try this where clause:

where year(table.date_field)=1753
Reply With Quote
  #4  
Old 27th April 2015, 14:12
vahdani's Avatar
vahdani vahdani is offline
Guru
 
Join Date: Aug 2002
Location: Cologne, Germany
Posts: 438
vahdani is on a distinguished road
Baan: all - DB: all - OS: Unix / Win2K
Hi Joy,

latest Porting set versions set an empty date field to January first 1970.

This seems to work:

select table.*
from table
where table.date_field = DATETIME2FROMPARTS (1970,1,1,0,0,0,0,0);
__________________
May the force be with you!
Reply With Quote
  #5  
Old 4th May 2015, 03:51
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,145
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Joy - can you post the solution for someone in the future. Maybe the two answers above are correct, but it might help someone in the future.
__________________
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
  #6  
Old 4th May 2015, 21:14
patvdv's Avatar
patvdv patvdv is offline
Board Master
 
Join Date: Aug 2001
Location: Belgium
Posts: 2,164
patvdv is on a distinguished road
Baan: n/a - DB: n/a - OS: AIX, HP-UX, Linux
Please do not delete threads where other members have contributed to a potential solution.
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2018 baanboard.com/baanforums.com
Reply With Quote
  #7  
Old 4th May 2015, 23:05
Joy Conner's Avatar
Joy Conner Joy Conner is offline
Guru
 
Join Date: Aug 2001
Posts: 375
Joy Conner is on a distinguished road
Baan: IVc4 - DB: MS SQL - OS: Windows Server 2008 & 2008 R2
Sooo sorry. Delete thread was an option that appeared to work. The solution to the problem was to fix a syntax error.

The execution was not done directly on the database.
My porting set is more current than 1970. The info is interesting but NA.

I apologize for causing an inconvenience. I deleted post the date I posted when I realized I asked a question too hastily.
Reply With Quote
Sponsored Links
  #8  
Old 5th May 2015, 07:57
patvdv's Avatar
patvdv patvdv is offline
Board Master
 
Join Date: Aug 2001
Location: Belgium
Posts: 2,164
patvdv is on a distinguished road
Baan: n/a - DB: n/a - OS: AIX, HP-UX, Linux
Quote:
Originally Posted by Joy Conner View Post
Sooo sorry. Delete thread was an option that appeared to work. The solution to the problem was to fix a syntax error.

The execution was not done directly on the database.
My porting set is more current than 1970. The info is interesting but NA.

I apologize for causing an inconvenience. I deleted post the date I posted when I realized I asked a question too hastily.
Hi Joy,

No worries. Deletion of threads is not prohibited but sharing knowledge is the ultimate goal even if the question was too hastily asked. Some of the replies may still be of value to other board users
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2018 baanboard.com/baanforums.com
Reply With Quote
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
Null Creation Date at inbound ASN - EDI magusmy Distribution, Transportation & Warehousing 0 10th January 2011 12:05
Null Date Error ODBC connection to MS Access rburgin Operating Systems & Databases 1 15th July 2010 16:08
Retrieve data from tdinv750 Drikus70 Operating Systems & Databases 2 9th September 2009 10:23
Baan null date field view from oracle sql zaidlaz Tools Development 9 21st June 2006 18:13


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


©2001-2018 - Baanboard.com - Baanforums.com