[Solved] How to get the right date format in query

[AfTech54]

Hi!

I'm about to store a large amount of information of photos in Base, among it the date of birth for persons that appear in the photos. Now I have a form and a date field where I enter the date of birth. I store it in the table "Persons" I where I have the format YYYY-MM-DD and all date also appear in that format. So so far everything looks good.
How ever if I execute a query including Person.DateOfBirth field I'll get the format YY-MM-DD. I can't figure out how to get the "right" format YYYY-MM-DD.

This is the query which sholud list a desription of all people mentioned in a specifc photo album.

SELECT DISTINCT "PE"."Name" AS "Namn", "PE"."DateOfBirth" AS "Födelsdatum", "PE"."ShortName" AS "Kortnamn", "PE"."Comment" AS "Kommentar"
FROM "Photos" AS "PH", "PhotoPerson" AS "PP", "Person" AS "PE", "Source" AS "S"
WHERE "PH"."ID_Photo" = "PP"."ID_Photo" AND "PP"."ID_Person" = "PE"."ID_Person"
AND "PH"."ID_Source" = "S"."ID_Source"
AND "S"."Source" = 'LEEL 1'

Any one that has a soltion for this litte issue?

Thougt I posted this question yesterday, but I couldn't find it so I posted it again. I hope a duplicate doesn't appear.

//Roger

[robleyd]

Presumably you are using the built-in hsqldb? See if there is a DATE_FORMAT function

[Villeroy]

Try this:
Open the "PE" table for editing (right-click>Edit) and click on the "DateOfBirth" field.
Near the bottom of the design window you can adjust the number format.

As far as I know, this format is used by default when you show the field in a query window, in the data source window or when you import the field into some office document.

[AfTech54]

ThankS!

As I wrote I've already chosen the YYY-MM-DD format. And the date in that table apeear as YYYY-MM-DD, but when I run a query I get YY-MM-DD.

//Roger

[UnklDonald418]

I'm not sure why your query isn't working. As Villeroy suggested, when I change the Date formatting in a table, a query on that table returns the Date in the format specified in the table.
Usually the query is used to supply the information for display on a form. A Date field on a form also has formatting options.
If it must appear in the query you should be able to force it with something like

Code: Select all

Year("DateOfBirth") || '-' || Month("DateOfBirth")|| '-' || Day("DateOfBirth") AS "BDay"

Of course BDay is no longer a date, it has been converted to a string.

[chrisb]

the function 'TO_CHAR' provides a convenient way to format the appearance of a date, time, date/time value.
to_char("PE"."DateOfBirth", 'YYYY-MM-DD')
Built-in functions and Stored Procedures

[AfTech54]

Ok and thanks!

I've formatted the date field in the form the same way as in the table?

The to_char functon should I use it in the "Select" part of the query and will it work in spite of the missing two (century) digits that could be 18.., 19.., or 20..?

//Roger

[chrisb]

yes just replace "PE"."DateOfBirth" in the select clause with to_char("PE"."DateOfBirth", 'YYYY-MM-DD')
the year will be shown as 4 digits & precicely reflect the value of year("DateOfBirth") as stored in your table.
the function does not alter your table values in any way it simply formats the query output.

[UnklDonald418]

will it work in spite of the missing two (century) digits that could be 18.., 19.., or 20..?

Yes, dates and times are stored as a number and calculated from a 0 starting point. According to Andrew Pitonyak

Date variables contain date and time values. OOo Basic stores dates internally as a Double. Dates, like all
numerical types, are initialized to zero, which corresponds to December 30, 1899 at 00:00:00.

so anything after that date and time is stored as a positive number, and anything prior is negative.

[AfTech54]

Thanks both!!

It works perfectly and thanks for the link (Built in func...)!

//Roger