Re: [SQL] max timestamp

Hi Michael, Try this.. ace=> create table test( mytime timestamp ); CREATE ace=> insert into test values (now() ); INSERT 1823542 1 ace=> insert into test values (now() ); INSERT 1823543 1 ace=> insert into test values (now()-1); INSERT 1823544 1 ace=> insert into test values (now()-1); INSERT 18

[SQL] Getting the week of a date

Dear Friends,   Postgres 7.3.4 on RH Linux7.2.   While this works for month and why not for week test=# select date_trunc('month',current_date + ('5 month')::INTERVAL); date_trunc- 2004-07-01 00:00:00(1 row)   test=# select date_trunc('week',current_date + ('5 month'

Re: [SQL] Getting the week of a date

When grilled further on (Mon, 16 Feb 2004 17:40:08 +0530), "Kumar" <[EMAIL PROTECTED]> confessed: > Dear Friends, > > Postgres 7.3.4 on RH Linux7.2. > > While this works for month and why not for week > date_trunc (obviously) doesn't support week. I ran into this a while ago, and came up with

Re: [SQL] Getting the week of a date

On Monday 16 February 2004 15:10, you wrote: > > Any idea on how to find the 3 rd Wednesday of any given month. SELECT 1-(to_char(date_trunc('month', now()::timestamp),'D'))::INT2 + 7*3-3 replace now with any date and you'll the the day number of a third Wed in that month. 7 is a constant (fac

[SQL] Date format problems

Hi im using the function below to insert data into my db; im using now() to get the timestamptz, however when inserted in the db the format seems to vary, the majority of the time its in the required European style but does spontaniously change to various other type can anyone throw any light on t

Re: [SQL] Getting the week of a date

On Monday 16 February 2004 12:10, Kumar wrote: > > test=# select date_trunc('week',current_date + ('5 month')::INTERVAL); > ERROR: TIMESTAMP units 'week' not supported Try EXTRACT(week FROM ...) -- Richard Huxton Archonet Ltd ---(end of broadcast)---

Re: [SQL] Getting the week of a date

EXCUSE ME, GUYS ! i forgot to add one monome: 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4) which is stands for skip a first week of month in case it is not consist Wed finally the select will be similar the following SELECT 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4) + 1

Re: [SQL] Date format problems

"Mark Roberts" <[EMAIL PROTECTED]> writes: > Hi im using the function below to insert data into my db; im using > now() to get the timestamptz, however when inserted in the db the format > seems to vary, the majority of the time its in the required European > style but does spontaniously change to

[SQL] Tip: a function for creating a remote view using dblink

Hello, I'm posting a function here in the hope others may find it useful and/or correct my mistakes/make improvements :) This creates a view of a remote table, using dblink: CREATE OR REPLACE FUNCTION dblink_create_view(text, text, text) RETURNS VOID LANGUAGE plpgsql STRICT AS ' DECLARE connst

Re: [SQL] Date format problems

Sure, sorry; Im using postgres version 7.2.1, and the column data type is 'timestamptz' Data examples: 13/02/04 12:35:27 appears in the column as 02/04/13 12:35:27, or 13/04/02 12:35:27 70% of the time it is inserted in the correct format. The function shown in previous email is called by a C

Re: [SQL] Date format problems

"Mark Roberts" <[EMAIL PROTECTED]> writes: > Sure, sorry; Im using postgres version 7.2.1, and the column data type > is 'timestamptz' > Data examples: > 13/02/04 12:35:27 appears in the column as 02/04/13 12:35:27, or > 13/04/02 12:35:27 > 70% of the time it is inserted in the correct format.

Re: [SQL] Function

On Mon, 16 Feb 2004, Sumita Biswas (sbiswas) wrote: > Thanks for the answer. > I have one more issue. How do I test a function that I wrote? > I was able to create a function called Proc_ConferenceSummary(). > In SQL Server I used to run it through query analyzer by writing the > following command

Re: [SQL] Getting the week of a date

Seems a part of your function always returns '0' select 1 - (to_char(date_trunc('month',now()),'D'))::INT2 because while we use date_trunc it will always return the first day of the month and when it get subtracted by '1' it be always zero. Is there any reason why you have included that? Thanks

Re: [SQL] Getting the week of a date

I am sorry. I didn't read the doc properly. I understood why it is written in that way. Sorry for the trouble. Thanks. It worked fine for me. Thanks Kumar - Original Message - From: "Kumar" <[EMAIL PROTECTED]> To: "sad" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, February 17,