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,

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.

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,

Perhaps because

quote:

it will NOT provide any support for 2008-specific functionality.

and DATETIME2 is 2008-specific.

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.

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,

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.