Impromptu Incorrect date used in SQL Server View

[GJParker]

Can anybody help me with this one ?

I have a view on the db which displays the relative year and period form a calendar table for todays date

CREATE VIEW dbo.Calendar_Financial_Period
AS
SELECT [Date] AS [Todays Date],
[Financial Year] AS [This Year],
[Financial Year] - 1 AS [Last Year],

CASE WHEN [Period No] <= 9 THEN CAST([Financial Year] AS char(4)) + '/0' + CAST([Period No] AS char(1)) WHEN [Period No] > 9 THEN CAST([Financial Year] AS char(4)) + '/' + CAST([Period No] AS char(2)) END AS [This Period],

CASE WHEN [Period No] = 1 THEN CAST([Financial Year] - 1 AS char(4)) + '/12' WHEN [Period No] <= 9 THEN CAST([Financial Year] AS char(4)) + '/0' + CAST([Period No] - 1 AS char(1)) WHEN [Period No] > 9 THEN CAST([Financial Year] AS char(4)) + '/' + CAST([Period No] - 1 AS char(2)) END AS [Last Period]

FROM dbo.Calendar
WHERE ([Date] = CONVERT(nchar(11), GETDATE(), 103))

When I run this on the db the results are correct for today

Todays Date = 05/12/2005
This Year = 2006
Last Year = 2005
This Period = 2006/08
Last Period = 2006/07

The problem occurs when I add this view to the impromptu catalog and run a report.

the SQL generated by the report is

select T1."Todays Date" AS "c1", T1."This Year" AS "c2", T1."Last Year" AS "c3", T1."This Period" AS "c4", T1."Last Period" AS "c5"
from "MIS"."dbo"."Calendar_Financial_Period" T1

The displayed results are

Todays Date = 12/05/2005
This Year = 2006
Last Year = 2005
This Period = 2006/01
Last Period = 2005/12

Regional settings on the PC are United Kingdom DD/MM/YYYY

cer4.ini entries are

[Locale]
localekey=en-gb
nativelocale=eng_gbr.1252
encoding=windows-1252

If I add the impromptu Today() function to a report then the correct date 05/12/2005 is dispalyed.

How do I get Impromptu to use the View correctly ?

Thanks in advance

Gary Parker
MIS Data Analyst
Manchester, England

 

[drlex]

I've gone with using NOW() rather than GETDATE() in my Views -would that make it use days as days, rather than as the month?

soi la, soi carré

 

[GJParker]

Hi drlex

As far as I know SQL Server 2000 doesn't support NOW() as a valid function.

The annoying thing here is that the view works fine when run from SQL query Analyser, MS Query and Crystal Reports it's only Impromptu that seems to transpose the day and month values.

I posted it to Cognos Support and they told me to read impromptu user reference on how to format date fields, I told them to read the question and try again. LOL

Gary

Gary Parker
MIS Data Analyst
Manchester, England

 

[drlex]

FWIW, my copy does support NOW [happy] - but then that's not much use for you , I guess. Only other Q is whether you're running the latest MDAC?

soi la, soi carré