Thursday 19 August 2010

The many faces of Datetime

Pope Gregory XIII, portrait by Lavinia FontanaImage via Wikipedia
As report writing professionals we, on a daily basis, have Ugo Boncompagni aka Pope Gregory XIII  to thank for a consistent and repeating annoyance.  Popes over the years have been attributed to bringing many weird and wonderful revelations to the world, this one in particular landed us with our calendar.  Good old Ugo.

Given that it has been knocking around since 1582, it's not doing too bad a job.  In fact it behaved itself for around 388 years, which is when things went wrong.  Fast forward to the beginning of the 1970s, IBM, no doubt binding dark forces together, developed a new computer language called SQL (known as SEQUEL at the time - but had to change it to avoid trademark infringement).

As Mr Boncompagni's calendar was slowly adopted across the globe, there were some countries who just had to be different.  You know the type of countries - those who never tucked their school shirts in and wore their tie all wrong.  So today, although we are using the same calendar, we are left with a dozen ways to write a date depending on where you are stood.

'So - what has that got to do with me?' I hear you asking.

If you are working on one or more databases that handle dates which only handles data relevant for your own country, and the database environment has been installed with all date related default settings aligned correctly then you should (mostly) be fine and don't need to read further. However, if you find yourselves working on international data then you may find something of interest.

Randomly picking a date from the calendar, say 5th April, you can write it:


Type Format
Long Date
5th, April 2010
Short Date
5th Apr 2010
British/French
5/4/2010
U.S.
4/5/2010
Japan
10/4/5


And a number of other ways also.

As you can see - a date can quickly lack any meaning depending on how it is presented.  As a preference I prefer using the long date, as it removes any question of doubt (defaulting to the short date if space is an issue).  However, opinions differ and more often than not you will need to present it as dd/mm/yy or yy/mm/dd etc.

In the aforementioned SQL, since MS SQL 2000, you are able to use the CONVERT function to handle your date formatting.

SYNTAX
======
CONVERT( {data_type} [ (length) ] , expression [ ,style ] )
ie
SELECT CONVERT(nvarchar(20) ,getdate() ,103)

The style argument is the one of interest to us.  The resultant data_type will have the date formatted as dictated by the style.  Using this function is a rather effective way to remove the time part from a datatime.


Without century (yy) (1) With century (yyyy) Standard Input/Output (3)
-
0 or 100 (1,2)
Default
mon dd yyyy hh:miAM (or PM)
1
101
U.S.
mm/dd/yyyy
2
102
ANSI
yy.mm.dd
3
103
British/French
dd/mm/yyyy
4
104
German
dd.mm.yy
5
105
Italian
dd-mm-yy
6
106(1)
-
dd mon yy
7
107(1)
-
Mon dd, yy
8
108
-
hh:mi:ss
-
9 or 109 (1,2)
Default + milliseconds
mon dd yyyy hh:mi:ss:mmmAM (or PM)
10
110
USA
mm-dd-yy
11
111
JAPAN
yy/mm/dd
12
112
ISO
yymmdd
yyyymmdd
-
13 or 113 (1,2)
Europe default + milliseconds
dd mon yyyy hh:mi:ss:mmm(24h)
14
114
-
hh:mi:ss:mmm(24h)
-
20 or 120 (2)
ODBC canonical
yyyy-mm-dd hh:mi:ss(24h)
-
21 or 121 (2)
ODBC canonical (with milliseconds)
yyyy-mm-dd hh:mi:ss.mmm(24h)
-
126 (4)
ISO8601
yyyy-mm-ddThh:mi:ss.mmm (no spaces)
-
127(6, 7)
ISO8601 with time zone Z.
yyyy-mm-ddThh:mi:ss.mmmZ
(no spaces)
-
130 (1,2)
Hijri (5)
dd mon yyyy hh:mi:ss:mmmAM
-
131 (2)
Hijri (5)
dd/mm/yy hh:mi:ss:mmmAM


There is also a CAST function, however you can not handle styles with it - so is not much use here.

So, say you have a datetime

SELECT CONVERT(datetime,'2010-06-17 01:23:45.000') as myDate

Gives you:

myDate
2010-06-17 01:23:45.000

However, you don't want the time and you want it in, say British/French format

SELECT CONVERT(nvarchar(20),CONVERT(datetime,'2010-06-17 00:00:00.000'),103) as myDate

Gives you:

myDate
17/06/2010

This proves particularly useful if you need to summarise by a date and have several times that need to be grouped together.

Another way to achieve the removal of a timestamp from datetime is :

select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

which is also a popular and fast way of doing it.






Enhanced by Zemanta

Friday 6 August 2010

Sing a Song of Synonym


With the risk of sounding too much like a Wikipedia entry - 'Synonyms' are words who have identical or very similar meanings with different words.  And what does that have to do with a budding Crystal Report writer?  Portability - that's what.  Setting up a synonym in a database is like instead of pointing at a table or a stored procedure you set up a pointer A -> B, (where B is the table/SP) however in another database you can point the same thing elsewhere ie A -> C.  As long as both table/stored procedure returns the required fields everything will be fine.

Supposing you are part of a development environment and have the luxury of multiple state instances (ie Development, Test, UAT, Live etc) in MS SQL 2005 or higher.  You would have therefore have experience of moving reports from one environment to another.

Now, on occasion (and it does happen, believe me) you will need the report to point to different tables/store procedures within the local database depending on the environment.  For example, on the test and development environments you require locally held table data, whereas on the live box you have an independant replicated reporting server and you need to point to that instead.

Here enters the humble synonym.  It creates a very handy abstraction layer.

So in the above example 'syn_DATA' points to [SERVER1].[DEV].[dbo].[DATA] on the dev box, however on the live box [SERVER3].[LIVE].[dbo] it would point to [REPORTS].[LIVE].[dbo].[DATA].  All of this is set at SQL Server level, so is transparent to the reports themselves.

This means, from a Crystal Reports point of view, that all you will need to do, when moving from one environment to another, is just to re-point the server and the job's a good 'n.

Provided that the Synonyms are set up on the database first Crystal Reports will be able to select them from the GUI.

I won't go into setting up a Synonym as there are reams of pages out there already covering that particular topic.
Enhanced by Zemanta

Thursday 5 August 2010

Exporting Crystal Reports into a legible, formatted Excel document

Don't.  Don't get me started.  You may as well ask me to turn lead into gold (which, ironically, I do know how to do - that's easy by comparisson). 

There is no easy way.

What you have is two completely separate and incompatible products whose only common theme is that they both hold data.  It would be like trying to word process in MS Paint (actually a funny story about my 7yr old daughter...another time).

'But there is an export function' I here you wimper.  Press it, see what happens.  At best, it is a rough approximation of the Crystal Report where it has put in a zillion columns and rows to mimic the formatting. (Bless, look it's trying to be helpful)  The problem here is that the primary reason you want it in Excel is to change stuff; add formulas, move stuff around etc. However, with all of this "formatting" it has made a dogs dinner of things and you may as well have typed it all in by hand.  The worst case is that it ramdanglifies the formatting in addition to the negative points above, so it is useless and not pretty.

If you are hell bent on accomplishing anything fancy then I suggest you export the data as csv, import this into MS Excel (or whatever) and use macros to handle the formatting.  (I have had quite good success in the past using this method).  Anything else is just overambitious ignorance and let me spare you the pain.

This is probably of not much use, however I am afraid to say that it is the truth.