From sgnerd@yahoo.com.sg Mon Feb  16 12:09:33 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 5D7CCD1D8AE
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 12:09:33 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 86561-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 08:09:33 -0400 (AST)
Received: from smtp105.mail.sc5.yahoo.com (smtp105.mail.sc5.yahoo.com
	[66.163.169.225])
	by svr1.postgresql.org (Postfix) with SMTP id 74964D1B49E
	for <pgsql-sql@postgresql.org>; Mon, 16 Feb 2004 08:09:29 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp105.mail.sc5.yahoo.com with SMTP; 16 Feb 2004 12:09:31 -0000
Message-ID: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "psql" <pgsql-sql@postgresql.org>
Subject: Getting the week of a date
Date: Mon, 16 Feb 2004 17:40:08 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00F4_01C3F4B3.EBC0F2C0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=BAYES_44,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200402/193
X-Sequence-Number: 16883

This is a multi-part message in MIME format.

------=_NextPart_000_00F4_01C3F4B3.EBC0F2C0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear Friends,

Postgres 7.3.4 on RH Linux7.2.

While this works for month and why not for week

test=3D# select date_trunc('month',current_date + ('5 month')::INTERVAL);
     date_trunc
---------------------
 2004-07-01 00:00:00
(1 row)

test=3D# select date_trunc('week',current_date + ('5 month')::INTERVAL);
ERROR:  TIMESTAMP units 'week' not supported
test=3D#

Any idea on how to find the 3 rd Wednesday of any given month.=20

Thanks
Kumar


------=_NextPart_000_00F4_01C3F4B3.EBC0F2C0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV><FONT size=3D2>Dear Friends,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Postgres 7.3.4 on RH Linux7.2.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>While this works for month and why not for=20
week</FONT></DIV><FONT size=3D2>
<DIV><BR>test=3D# select date_trunc('month',current_date + ('5=20
month')::INTERVAL);<BR>&nbsp;&nbsp;&nbsp;&nbsp;=20
date_trunc<BR>---------------------<BR>&nbsp;2004-07-01 00:00:00<BR>(1=20
row)</DIV>
<DIV>&nbsp;</DIV>
<DIV>test=3D# select date_trunc('week',current_date + ('5=20
month')::INTERVAL);<BR>ERROR:&nbsp; TIMESTAMP units 'week' not=20
supported<BR>test=3D#</DIV>
<DIV>&nbsp;</DIV>
<DIV>Any idea on how to find the 3 rd Wednesday of any given month. </DIV>
<DIV>&nbsp;</DIV>
<DIV>Thanks</DIV>
<DIV>Kumar</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<P></P></BODY></HTML>

------=_NextPart_000_00F4_01C3F4B3.EBC0F2C0--




From Robert_Creager@LogicalChaos.org Mon Feb  16 12:44:27 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id CEE00D1DD21
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 12:44:27 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 08158-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 08:44:28 -0400 (AST)
Received: from srvr3.iniquinet.com (srvr2.iniquinet.com [64.240.87.12])
	by svr1.postgresql.org (Postfix) with SMTP id 0DFD8D1DCD6
	for <pgsql-sql@postgresql.org>; Mon, 16 Feb 2004 08:44:24 -0400 (AST)
Received: (qmail 11111 invoked by uid 104); 16 Feb 2004 12:30:09 -0000
Received: from Robert_Creager@LogicalChaos.org by srvr3.iniquinet.com by uid
	101 with qmail-scanner-1.15
	(clamscan: 0.65. spamassassin: 2.55.  Clear:SA:0(-7.2/6.0):.
	Processed in 36.66118 secs); 16 Feb 2004 12:30:09 -0000
Received: from vsat-148-64-8-86.c119.t7.mrt.starband.net (HELO
	chaos.mshome.net) (148.64.8.86)
	by srvr3.iniquinet.com with SMTP; 16 Feb 2004 12:29:32 -0000
Received: from localhost.localdomain (thunder.mshome.net [192.168.0.250])
	by chaos.mshome.net (Postfix) with ESMTP
	id 8C5D91E456F; Mon, 16 Feb 2004 05:43:44 -0700 (MST)
Received: from logicalchaos.org (thunder.mshome.net [192.168.0.250])
	by chaos.mshome.net (Postfix) with SMTP
	id 90CB718B911; Mon, 16 Feb 2004 05:43:40 -0700 (MST)
Date: Mon, 16 Feb 2004 05:43:20 -0700
From: Robert Creager <Robert_Creager@LogicalChaos.org>
To: "Kumar" <sgnerd@yahoo.com.sg>
Cc: "psql" <pgsql-sql@postgresql.org>
Subject: Re: Getting the week of a date
Message-Id: <20040216054320.32df9226.Robert_Creager@LogicalChaos.org>
In-Reply-To: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
References: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
Organization: Starlight Vision, LLC.
X-Mailer: Sylpheed version 0.9.5claws (GTK+ 1.2.10; i586-mandrake-linux-gnu)
Mime-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
	micalg="pgp-sha1";
	boundary="Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG"
	USER_IN_WHITELIST autolearn=ham version=2.63
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/194
X-Sequence-Number: 16884

--Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG
Content-Type: text/plain; charset=US-ASCII
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

When grilled further on (Mon, 16 Feb 2004 17:40:08 +0530),
"Kumar" <sgnerd@yahoo.com.sg> 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 this function.  I left the function signature the same as
date_trunc, even though I don't use the first argument.  I did only minor
testing (10 years or so), so no guarantee about it's correctness.  And it's kind
of slow...

CREATE OR REPLACE FUNCTION date_trunc_week( text, timestamp )
RETURNS timestamp AS '
DECLARE
   reading_time ALIAS FOR $2;
   year timestamp;
   dow integer;
   adjust text;
   week text;
BEGIN
   year := date_trunc( ''year''::text, reading_time );
   week := date_part( ''week'', reading_time ) - 1 || '' week'';
   dow := date_part( ''dow'', year );
   -- If the dow is less than Thursday, then the start week is last year
   IF dow <= 4 THEN
      adjust := 1 - dow || '' day'';
   ELSE
      adjust := 8 - dow || '' day'';
   END IF;
   RETURN year + adjust::interval + week::interval;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;


-- 
 05:37:49 up 1 day, 13:20,  2 users,  load average: 0.09, 0.36, 0.63
Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003

--Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iEYEARECAAYFAkAwuvwACgkQLQ/DKuwDYzm2xACdGIajt5kT/heTMhmiyIqa63Gb
9isAn2WgpI2A2GLiOaLFLbyjjZMmy6JS
=Hhtd
-----END PGP SIGNATURE-----

--Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG--




From dev@archonet.com Mon Feb  16 13:03:30 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 6F965D1B456
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 13:03:30 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 06514-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 09:03:31 -0400 (AST)
Received: from anchor-post-37.mail.demon.net (anchor-post-36.mail.demon.net
	[194.217.242.86])
	by svr1.postgresql.org (Postfix) with ESMTP id D02CBD1D8B7
	for <pgsql-sql@postgresql.org>; Mon, 16 Feb 2004 09:03:12 -0400 (AST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-37.mail.demon.net with esmtp (Exim 3.35 #1)
	id 1AsiP5-000Gdr-0b; Mon, 16 Feb 2004 13:03:16 +0000
Received: by mainbox.archonet.com (Postfix, from userid 529)
	id 4D7CB16621; Mon, 16 Feb 2004 13:03:14 +0000 (GMT)
Received: from client17.archonet.com (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 192F91633C; Mon, 16 Feb 2004 13:03:13 +0000 (GMT)
From: Richard Huxton <dev@archonet.com>
To: "Kumar" <sgnerd@yahoo.com.sg>, "psql" <pgsql-sql@postgresql.org>
Subject: Re: Getting the week of a date
Date: Mon, 16 Feb 2004 13:03:12 +0000
User-Agent: KMail/1.5
References: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
In-Reply-To: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200402161303.12330.dev@archonet.com>
X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/195
X-Sequence-Number: 16885

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



From sad@bankir.ru Mon Feb  16 13:04:22 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 89F06D1DD17
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 13:04:22 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 05206-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 09:04:22 -0400 (AST)
Received: from rock.spider.ru (rock.spider.ru [62.183.34.226])
	by svr1.postgresql.org (Postfix) with ESMTP id 25D0DD1DA97
	for <pgsql-sql@postgresql.org>; Mon, 16 Feb 2004 09:04:18 -0400 (AST)
Received: from rock (localhost [127.0.0.1])
	by rock (Postfix) with SMTP id BD5309127B
	for <pgsql-sql@postgresql.org>; Mon, 16 Feb 2004 16:04:19 +0300 (MSK)
Received: from sad.localnet.ru (sad.localnet.ru [10.0.0.33])
	by rock.spider.ru (Postfix) with ESMTP id 5048291267
	for <pgsql-sql@postgresql.org>; Mon, 16 Feb 2004 16:04:19 +0300 (MSK)
Content-Type: text/plain;
  charset="iso-8859-1"
From: sad <sad@bankir.ru>
Organization: IT
To: pgsql-sql@postgresql.org
Subject: Re: Getting the week of a date
Date: Mon, 16 Feb 2004 16:08:52 +0300
User-Agent: KMail/1.4.3
References: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
In-Reply-To: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200402161608.52404.sad@bankir.ru>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/196
X-Sequence-Number: 16886

On Monday 16 February 2004 15:10, you wrote:
>
> Any idea on how to find the 3 rd Wednesday of any given month.

SELECT=20
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 t=
hat=20
month.

7 is a constant (factor 3 is a desired week number)
-3 is number of days to step back from sunday to a desired day of week
(-3 stands for Wed)





From sad@bankir.ru Mon Feb  16 13:18:50 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 6BFE5D1DAB0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 13:18:50 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 05416-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 09:18:51 -0400 (AST)
Received: from rock.spider.ru (rock.spider.ru [62.183.34.226])
	by svr1.postgresql.org (Postfix) with ESMTP id 1339DD1D3DC
	for <pgsql-sql@postgresql.org>; Mon, 16 Feb 2004 09:18:48 -0400 (AST)
Received: from rock (localhost [127.0.0.1])
	by rock (Postfix) with SMTP id 1772091285
	for <pgsql-sql@postgresql.org>; Mon, 16 Feb 2004 16:18:51 +0300 (MSK)
Received: from sad.localnet.ru (sad.localnet.ru [10.0.0.33])
	by rock.spider.ru (Postfix) with ESMTP id E7A2191267
	for <pgsql-sql@postgresql.org>; Mon, 16 Feb 2004 16:18:50 +0300 (MSK)
Content-Type: text/plain;
  charset="iso-8859-1"
From: sad <sad@bankir.ru>
Organization: IT
To: pgsql-sql@postgresql.org
Subject: Re: Getting the week of a date
Date: Mon, 16 Feb 2004 16:23:24 +0300
User-Agent: KMail/1.4.3
References: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
	<20040216054320.32df9226.Robert_Creager@LogicalChaos.org>
In-Reply-To: <20040216054320.32df9226.Robert_Creager@LogicalChaos.org>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200402161623.24195.sad@bankir.ru>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/198
X-Sequence-Number: 16888

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 -=20
(to_char(date_trunc('month',now()),'D'))::INT2  +  7*3-3 ;

4 - is a number of Wed in a week (in postgresql numeration)





From sgnerd@yahoo.com.sg Tue Feb  17 05:09:46 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 9C4A8D1DAB1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Feb 2004 05:09:46 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 18566-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Feb 2004 01:09:39 -0400 (AST)
Received: from smtp018.mail.yahoo.com (smtp018.mail.yahoo.com
	[216.136.174.115])
	by svr1.postgresql.org (Postfix) with SMTP id 33C33D1D174
	for <pgsql-sql@postgresql.org>; Tue, 17 Feb 2004 01:09:38 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp018.mail.yahoo.com with SMTP; 17 Feb 2004 05:09:36 -0000
Message-ID: <00a401c3f514$58244970$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "sad" <sad@bankir.ru>, <pgsql-sql@postgresql.org>
References: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
	<20040216054320.32df9226.Robert_Creager@LogicalChaos.org>
	<200402161623.24195.sad@bankir.ru>
Subject: Re: Getting the week of a date
Date: Tue, 17 Feb 2004 10:40:19 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/204
X-Sequence-Number: 16894

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
Kumar

----- Original Message ----- 
From: "sad" <sad@bankir.ru>
To: <pgsql-sql@postgresql.org>
Sent: Monday, February 16, 2004 6:53 PM
Subject: 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 -
(to_char(date_trunc('month',now()),'D'))::INT2  +  7*3-3 ;

4 - is a number of Wed in a week (in postgresql numeration)



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly




From sgnerd@yahoo.com.sg Tue Feb  17 05:46:17 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 1C004D1D8F3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Feb 2004 05:46:17 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 22659-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Feb 2004 01:46:13 -0400 (AST)
Received: from smtp101.mail.sc5.yahoo.com (smtp101.mail.sc5.yahoo.com
	[216.136.174.139])
	by svr1.postgresql.org (Postfix) with SMTP id DFDDED1D8B7
	for <pgsql-sql@postgresql.org>; Tue, 17 Feb 2004 01:46:11 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp101.mail.sc5.yahoo.com with SMTP; 17 Feb 2004 05:46:11 -0000
Message-ID: <00de01c3f519$743406a0$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "sad" <sad@bankir.ru>, <pgsql-sql@postgresql.org>
References: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
	<20040216054320.32df9226.Robert_Creager@LogicalChaos.org>
	<200402161623.24195.sad@bankir.ru>
	<00a401c3f514$58244970$7502a8c0@hdsc.com>
Subject: Re: Getting the week of a date
Date: Tue, 17 Feb 2004 11:16:53 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/205
X-Sequence-Number: 16895

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" <sgnerd@yahoo.com.sg>
To: "sad" <sad@bankir.ru>; <pgsql-sql@postgresql.org>
Sent: Tuesday, February 17, 2004 10:40 AM
Subject: 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
> Kumar
>
> ----- Original Message ----- 
> From: "sad" <sad@bankir.ru>
> To: <pgsql-sql@postgresql.org>
> Sent: Monday, February 16, 2004 6:53 PM
> Subject: 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 -
> (to_char(date_trunc('month',now()),'D'))::INT2  +  7*3-3 ;
>
> 4 - is a number of Wed in a week (in postgresql numeration)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster