Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL 2008 DATETIME2
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] SQL 2008 DATETIME2
 Login/Join
 
Gold member
posted
Hi,

Has anybody used this new data type DATETIME2 for SQL Server 2008 within a master file?

When creating a master file it is coming out as :
 
FIELDNAME=START, ALIAS=start, USAGE=A27V, ACTUAL=A27V, FIELDTYPE=R, MISSING=ON, $ 


When using the old data type from SQL 2005, DATETIME it came out as:

     
FIELDNAME=START, ALIAS=start, USAGE=HYYMDs, ACTUAL=HYYMDs,
MISSING=ON, $ 


Just wondering why it is not being picked up as HYYMDs?

Jinx.

This message has been edited. Last edited by: Jinx,


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
According to Tech Support Symptom/Problem/Solution Case 61582006 - SQL Server 2008 R2 compatibility:

quote:
Problem:

Is 7610 compatibile with SQL Server 2008 R2?

Solution:

Yes and there are no known problems or issues.

In 76x, it is necessary to use the SQL Server 2005 Data Adapter which is configured with the SQL Server 2005 Client Software. The 2005 Data Adapter can access a SQL Server 2008 Instance and retrieve data, but it will NOT provide any support for 2008-specific functionality.


Tech Support emphasis on the NOT.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Master
posted Hide Post
quote:
Originally posted by Jinx:
Hi,

Has anybody used this new data type DATETIME2 for SQL Server 2008 within a master file?

When creating a master file it is coming out as :
 


FIELDNAME=START, ALIAS=start, USAGE=A27V, ACTUAL=A27V, FIELDTYPE=R, MISSING=ON, $ 


When using the old data type from SQL 2005, DATETIME it came out as:

     
FIELDNAME=START, ALIAS=start, USAGE=HYYMDs, ACTUAL=HYYMDs,
MISSING=ON, $ 


Just wondering why it is not being picked up as HYYMDs?

Jinx.


Workaround to use a view that wraps the datetime2 as a datetime (via cast/convert)?

- ABT

This message has been edited. Last edited by: ABT,


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Perhaps because
quote:
it will NOT provide any support for 2008-specific functionality.
and DATETIME2 is 2008-specific.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Good point Francis, that didn't even occur to me since DATETIME2 is 'pretty much' the same as DATETIME. I need to use DATETIME2 as is sorts out the problem of UK language settings with the YYYY-MM-DD date format.
Actually, my signature is a bit out of date (will change in a minute) as we are now on 7.6.11. I assumed that since IBI upgraded us recently, specifically becasue we use SQL 2008 that this version would support 2008 functionality..stupid me didn't go and look the the release notes.

Thanks,

Jinx.


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Reply With QuoteReport This Post
Gold member
posted Hide Post
We often change the master files (as below), so I tried this with the new DATETIME2 to test if a report using the date would work in the same way as DATETIME...
 FIELDNAME=START, ALIAS=start, DMYY, DATE, MISSING=ON, $  

It worked with the following :
 TABLE FILE TIME
PRINT START 
WHERE START GT '08/01/2008'
AND START LT '10/01/2008'
END
 


But unfortunately it does not work with 'EQ':
 TABLE FILE TIME
PRINT START 
WHERE START EQ '09/01/2008'
END
 

So it seems trying to use a data type that is not support is dodgy business at best!
Back to the drawing board then.

Jinx.

This message has been edited. Last edited by: Jinx,


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Reply With QuoteReport This Post
Master
posted Hide Post
Jinx,

If the real field in the database is a datetime field, EQ will not work if the time portion of the field is not midnight and the database is doing the WHERE test. You have to look at the SQL being generated. When WebFOCUS uses a WHERE clause like you did, 8/1/2008 is used as 8/1/2008 at 00:00:00.00000 (which is midnight), so if your field has 8/1/2008 at 7:24:00.00000, the dates are not equal so the EQ test fails.

So, your first query will not work if any row has midnight as the time. Your query should be:
TABLE FILE TIME
PRINT START 
WHERE START GE '08/01/2008'
AND START LT '10/01/2008'
END


However, if for some reason WebFOCUS does the WHERE test, it will work just fine since you have defined START as MDYY, WebFOCUS will ignore the time portion of the field. That is why you have to review the SQL being generated to see where the WHERE test is being done.

Actually, in our .mas files, we will take a datetime field and define it twice:
FIELDNAME=BEGDTTM, ALIAS=begin_dt, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $
FIELDNAME=BEGDT  , ALIAS=begin_dt, USAGE=YYMD  , ACTUAL=DATE  , MISSING=ON, $


This way, if I need to, I can sort by BEGDTTM and select based on BEGDT. As a standard, when wanting a set of rows and selecting by date we always use the where date GE 'date1' and date LT 'date2'. This way we don't care if the database is doing the selection or if WebFOCUS is doing it, we always get the correct return set.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 972 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL 2008 DATETIME2

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.