Hands On: Dates and databases

Not long ago, I was working on a database that contained a table with a couple of date columns (nothing strange there) and my task was to transfer this data into a table in an entirely different DBMS.

The dates were stored with both date and time components (05/02/2009 11:14:35, for instance), but the system into which they were to be transferred would only accept the date portion.

I thought about converting the date/times into a string, extracting the date part and converting it back into a date with the Datevalue function, perhaps with an Access expression such as this:

DateOnlyOrderDateComplex: DateValue(Left(Str([Order Date]),10))

This uses the String function to turn the contents of the Order Date column from date/time data into a string. The Left function extracts 10 characters from the string counting from the left, for example, reducing 05/02/2009 11:14:35 to the 10 characters 05/02/2009. Finally, Datevalue turns that string back into a date without the time component.

But then I remembered an obscure (well, obscure to me) party trick that Datevalue can perform, which I had to look up to verify. Datevalue is normally used to convert strings to dates, and so one normally feeds it strings of characters. But if you feed it date and time data instead, it will return just the date element. It simply ignores the time element.

My Access expression can be simplified in a very satisfactory way to:

DateOnlyOrderDate: DateValue([Order Date])

And this is the SQL (the sample table Orders and the query TimelessOrderDate are in DbcFeb09.mdb, which you’ll find on this month’s cover disc):

SELECT Orders.OrderID, Orders.[Order Date], DateValue([Order Date]) AS DateOnlyOrderDate
FROM Orders;

Decimals, precision, scale
Displaying decimals in Access has been giving reader Jerry Ward a headache. His straightforward requirement is to store values such as 12.345, 123.45, 1.23456 and 12.34567.

When designing his table, he created a Number type column, set its Field Size to Decimal and the Decimal Places property to 5. Into his table he entered the value 1.23456, expecting to see just that. But no, the numbers appeared as he typed, yet everything after the decimal place vanished as soon as the focus left the cell. Not unreasonably, he wants to know why.

Jerry’s problem is that he is being not only reasonable, but also logical. You’d think that setting the property called Decimal Places would do the job, especially as the explanatory text alongside reads, ‘The number of digits that are displayed to the right of the decimal separator’. But let’s take a step back. The Decimal field size has two other properties that are worth investigating: Precision and Scale.