Crystal Reports: Date Literals

	Resources
	Best sellers: cView Report Analyzer cViewSERVER ReCrystallize

	Articles: Administration Advanced Basic Crystal eNL Database Financial Problems Solved Books: CR Books Database Books Developer Books
	Tools: Analyzers Bestsellers CR Schedulers CR UFLs CR Viewers DataBase Tools Graphics International Mail UFLs ReCrystallizePro
	Add'l: About us Contact Us cViewSUITE Ppt Support
	CrystalReports on Steroids

Crystal Reports: Date Literals

We are seeing examples of formulas that use date literals. These are numbers that have the following format:

#6/12/2004#

The major problem is that these have been developed by US-based programmers who think the world uses M/D/Y formatted dates. So while a USA reader will expect the above date to be 12 June, a reader in Australia will see 6 December.

The problem escalates if your report moves from a machine with one regional format into another. Windows handles these things with “Regional Settings” and you might expect the date literals to follow the same format. But they don’t. Even with a D/M/Y regional setting, the date literal appears to use the M/D/Y format unless the first number is greater than 12. So you get the strange behavior as below:

#7/9/04# is 9 July
#17/9/04# is 17 September

If you use DMY regional settings, use an alphabetic month to avoid this confusion. #7-Sep-04# will process the date for the correct month and avoid any confusion.

Let's talk more about date formatting.

Suppose you are communicating with someone who does business internationally and ask for an item to be delivered "on 03/09/2006." When can you expect that item to arrive? You may be thinking you are going to get it on March 9th. But, the other person reads this as 03 September.

What can you do to avoid problems? A commonly accepted solution is to use MMM to indicate the month. Three upper case letters in a date format means you are using a three-letter designation for the month. For example, 03SEP2006. There's no confusion as to what month you can expect delivery.

This method of mixing alpha and numeric solves a communication problem quite handily. But what about inside a database or spreadsheet, where you need to do calculations with dates? It's a bit hard to subtract FEB from NOV, now, isn't it? So how do you guarantee you are going to get the month and the day in the correct order?

In software--especially in databases, file systems, and spreadsheets, another format is fairly standard and it solves that problem. You'll also see this format in inventory systems and many other tabular style applications. That format is YYYY-MM-DD. A variation is YYYYMMDD.

So, it will look like this: 20060903. The largest unit (years) are on the left, and the smallest units (days) are on the right. Why not do it the other way? Well, you could. But what if you wanted to sort by date? Or perform a calculation that involved different months or years?

To avoid confusion:

Use the YYYYMMDD format when working with data.
Use the DDMMMYYYY format when working with information.
Don't use the MMDDYYYY format at all.

To avoid related confusion:

Always use four digits for the year, not two.
Always provide a legend for a date mask, when you are forcing the format for data validation purposes.
When providing a legend for a date mask, remember that lower case letters indicate hours, minutes, and seconds: YYYYMMDDhhmmss.

If you are using the 12-hour clock, you should consider going to a 24-hour time standard. This also avoids confusion, especially if you run a multishift or 24-hour operation.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.