SQL Date Formatting

[Ken Ray]

> 3) I love the fact I don't have to take into account if a user is using
> mm/dd/yy format or dd-mm-yy format, once it converted to dateItems it's the
> same on EVERY system.

Just a little 'gotcha' here - I've been doing A LOT with dates and times recently, and discovered that you either need to make sure the 'useSystemDate' is set to true in your code, OR make sure to add the "system" keyword in any format conversion, otherwise it assumes US-formatted dates (this is documented in Bug #9893 in Bugzilla). Here's an example:

If you're in the UK, and it's June 1, 2011, and you don't have "useSystemDate" active, if you:

	put the short date
	--> 6/1/11   (US-formatted)

	put the short system date
	--> 01/06/2011    (UK-formatted)

If you do this:

	put the short system date into tDate
	convert tDate to dateItems

you get this:

	2011,1,6,0,0,0,5

Which makes it *look* like it's reversing the month and day positions, but what's actually happening is the convert command hasn't been *told* that you're using a "system" date, so it attempts to convert "01/06/2011" as if it was a US date and ends up the way you see it here. To solve the problem you'd need to:

	put the short system date into tDate
	convert tDate from short system date to dateItems  -- you can also do "system date" instead of "short system date"
	--> 2011,6,1,0,0,0,5

So it's very important when dealing with non-US dates that you turn "useSystemDate" on OR use the "system" keyword otherwise you'll end up with a lot of headaches…


Ken Ray
Sons of Thunder Software, Inc.
Email: kray at sonsothunder.com
Web Site: http://www.sonsothunder.com/