Wednesday, April 19, 2006

DATEADD

MSSQL
select dateadd(year,1, '2004-2-29')

PostgreSQL
select '2004-2-29'::date + cast('1 years' as interval)

MSSQL:
select dateadd(month,1,'2004-2-29')


PostgreSQL:
select '2004-2-29'::date + cast('1 months' as interval)

MSSQL:
select dateadd(month,1, '1976-11-05 6:29 AM')

PostgreSQL:
select '2006-01-06 6:29 AM'::timestamptz + cast('1 months' as interval)

MSSQL:
select dateadd(day,1,'2006-01-06 6:29 AM')
-- or --
select convert(datetime,'2006-01-06 6:29 AM') + 1


PosgtgreSQL:
select '2006-01-06 6:29 AM'::timestamp + cast('1 days' as interval);

------------------------------------------------------------------------
------------------------------------------------------------------------
MS SQL equivalent compatible DATEADD function

-- Function: dateadd(character varying, integer, date)

-- DROP FUNCTION dateadd(character varying, integer, date);

CREATE OR REPLACE FUNCTION dateadd(character varying, integer, date)
RETURNS date AS
$BODY$

DECLARE
p_Interval ALIAS FOR $1;
p_N ALIAS FOR $2;
p_Date ALIAS FOR $3;




BEGIN
if p_Interval = 'm' then
return p_Date + cast(p_N || ' months' as interval);
elseif p_Interval = 'y' then
return p_Date + cast(p_N || ' years' as interval);
else
raise exception 'dateadd interval parameter not supported';
-- raise exception 'hello';
return null;
end if;
END;
$BODY$
LANGUAGE 'plpgsql';

Labels: ,

7 Comments:

Anonymous Anonymous said...

Denks!

7:05 PM

 
Anonymous Anonymous said...

Thank you

12:55 AM

 
Blogger Milap Doshi said...

This comment has been removed by the author.

2:07 PM

 
Blogger Milap Doshi said...

Thanx MAn, it helped a lot...

2:08 PM

 
Anonymous Anonymous said...

[url=http://vtyupdr.com]iczqCmAnCvAWaJ[/url] , bnkSF , http://iluubcb.com

9:50 PM

 
Anonymous Anonymous said...

Fantastic post however , I was wanting to know if you could write a litte more on this subject?
I'd be very grateful if you could elaborate a little bit more. Thank you!

my site; online roulette

10:12 PM

 
Anonymous Anonymous said...

Thanks!!!

4:46 PM

 

Post a Comment

Links to this post:

Create a Link

<< Home