Thread: How Can I set a non standard date format?
Hi All Esteemed Developers, I have a requirement to have dates like ddmmmyyy. ie: 03MAR07 I want to be able to have dates returned from a query in this format so that I can use passthrough queries from Access. I know I can use to_char to format the date anyway I want, but this causes the column to appear as a text datatype instead of a date datatype. I am exporting the data to Excel, and the columns that have dates formatted as text do not sort properly. That is why I want the column to remain a date type. In Oracle I could use NLS_TIMESTAMP_FORMAT to accomplish this. But I cannot find a way to use PGDATESTYLE or anything else to to the same thing. Any ideas? best regards, billc
On 2 Mar 2007 11:20:28 -0800, clark@knowideas.com <clark@knowideas.com> wrote: > Hi All Esteemed Developers, > > I have a requirement to have dates like ddmmmyyy. ie: 03MAR07 > > I want to be able to have dates returned from a query in this format > so that I can use passthrough queries from Access. I know I can use > to_char to format the date anyway I want, but this causes the column > to appear as a text datatype instead of a date datatype. > > I am exporting the data to Excel, and the columns that have dates > formatted as text do not sort properly. That is why I want the column > to remain a date type. > > In Oracle I could use NLS_TIMESTAMP_FORMAT to accomplish this. But I > cannot find a way to use PGDATESTYLE or anything else to to the same > thing. > > Any ideas? http://www.postgresql.org/docs/8.2/interactive/ecpg-pgtypes.html PGTYPESdate_fmt_asc Best of luck. > best regards, > billc Cheers, Andrej
clark@knowideas.com wrote: > Hi All Esteemed Developers, > > I have a requirement to have dates like ddmmmyyy. ie: 03MAR07 > > I want to be able to have dates returned from a query in this format > so that I can use passthrough queries from Access. I know I can use > to_char to format the date anyway I want, but this causes the column > to appear as a text datatype instead of a date datatype. Well, 03MAR07 is not a valid date. This is the formats we understand: http://www.postgresql.org/docs/8.2/static/datatype-datetime.html Joshua D. Drake > Any ideas? > > best regards, > billc > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
clark@knowideas.com wrote: > Hi All Esteemed Developers, > > I have a requirement to have dates like ddmmmyyy. ie: 03MAR07 > > I want to be able to have dates returned from a query in this format > so that I can use passthrough queries from Access. I know I can use > to_char to format the date anyway I want, but this causes the column > to appear as a text datatype instead of a date datatype. > > I am exporting the data to Excel, and the columns that have dates > formatted as text do not sort properly. That is why I want the column > to remain a date type. Have you tried formatting the dates as yyyymmdd? It will allow excel to sort the dates properly even if it sees them as numbers or text. My guess is that you may find it easier to send dates out as a standard dd/mm/yyyy and have excel format them to display any way you want. Select the excel column with the dates and goto format cells if you select custom you can set the display format to ddmmmyyy or any other display you may think of. > In Oracle I could use NLS_TIMESTAMP_FORMAT to accomplish this. But I > cannot find a way to use PGDATESTYLE or anything else to to the same > thing. > > Any ideas? > > best regards, > billc > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > -- Shane Ambler pgSQL@Sheeky.Biz Get Sheeky @ http://Sheeky.Biz