|
The technical forum of the NBN Trust for use by the Network. It includes discussions and announcements on the NBN Atlas, Recorder 6 and Indicia |
You are not logged in. Please login or register.
Forum → Reporting & XML Reports → Date format in HTML and XML export
In and HTML or XML export the dates come out as serial numbers (e.g. 07/07/1992 appears as 33792). Is there any way for me to modify the date format? If not, can I request that this is changed?
Many thanks.
Alan
I think this can be done with a user defined function (see reply to dates query), however, it isn't something I have yet done.
Mike
Mike - I think you have mixed up your replies to these two topics (dates and ampersands). Anyway, thanks for the link to your LCReturnVagueDate function. I'm running Recorder 6 on SQL Server Express 2005 and have installed the function through SQL Server Management Tools ( I had to change "ALTER FUNCTION" to "CREATE FUNCTION" in the script.).
I haven't yet figured out exactly how to proceed further so I may come back with further questions. I'll give it some more thought first.
Cheers
Alan
Mike
OK, I think I can see in principle how to use this function in an XML report. I've tried modifying one of your XML reports but can't seem to get the syntax right. Is it only necessary to modify the main SELECT statement?
If it's not too much trouble could you explain exactly how? I've tried just inserting:
,dbo.LCVagueDateShort(s.vague_date_start,s.vague_date_end,s.vague_date_type) as Vague date,
into the statement.
Many thanks
Alan
Alan
If you have actually used 'Vague date' and not vague_date as the field name this could be the problem. If this isn't it please let me know which report you were trying alter and I will adapt it for you as a demonstration.
Mike
As long as the date is not before 1753, you can just use the SQL:
dateadd(day, s.vague_date_start, '1899-12-30')This simply converts the numeric date value to a real date type in SQL Server. There's lots of info on vague dates on the Wiki at :
http://eim.metapath.org/wiki/index.php?title=Vague_Dates
Alan
Just noticed that the function is actually dbo.LCReturnVagueShort so query should be
dbo.LCReturnVagueDateShort(s.vague_date_start,s.vague_date_end,s.vague_date_type) as Vague_date,
Mike
Thanks Mike - that explains why it couldn't find the function when I tried last night!
You've actually got it slightly wrong here; it should be:
dbo.LCReturnVagueDate(s.vague_date_start,s.vague_date_end,s.vague_date_type) as Vague_date
So, I've inserted this, modified the Main Table and Column Names, but I now get the error:
EXECUTE permission denied on object LCReturnVagueDate, database 'NBNData', schema 'dbo'
By the way, it's your "LC1Detailed Species List 1or10kmV2.xml" report I'm experimenting with.
Cheers
Alan
I've just tried the method sugested by John Vandebreda
dateadd(day, s.vague_date_start, '1899-12-30')and this works fine.
I would still like to understand what the problem is with running Mike's function however.
Many thanks
Alan
I've just noticed one problem with the "dateadd" method. Actual "vague" dates (e.g. "Feb 2003" or "2006") get rendered as precise dates (e.g. "1 Feb 2003"; "1 Jan 2006"). Is there a way round this?
Thanks
Alan
Alan
I can see what has happened now. I pointed you to the wrong file on the web site. The correct one was www.Lfield.Co.Uk/downloads/LCReturnVagueDateScript.zip. This should contain a script called LCReturnVagueDateShort. W ithin the script is a statement to set the permission on the function to public. The function you have installed should work, but you will need to manually change the permissions to public. Incidentally neither will work for centuries.
My script and the solution given by John are doing different things. John's solution is just converting the number to a date which is fine if you are not using vague dates. You also have watch for dates of type U (unknown ) as these will report as 30/12/1899 unless you specifically allow for them. Also for dates before 1/1/1753 which it will not cope with. The most appropriate approach depends on your data.
Normally you don't need either method in XML reports as they handle the vague dates. Export to Excel retains this formatting, but for some reason this isn't the case with exports to xml or html.
Mike
Mike
Thanks very much, this is working fine now. I had realised that the export to Excel works OK, but I'm currently interested in export to XML for further manipulation.
My question now is - can the date format be fixed for reports generated out of the Report Wizard (as opposed to XML reports)?
Alan
Incidentally, this has provided a very useful insight for me into the power of the programmability of SQL Server. Thanks.
Alan
MSSQL 2005 actually goes much further and allows you to write .NET functions. Also, XML funtionality within MSSQL 2005 has been greatly improved.
Forum → Reporting & XML Reports → Date format in HTML and XML export
Powered by PunBB 1.4.6, supported by Informer Technologies, Inc.