Re: calculate the midle and end of the month
From: John Gilson (jag_at_acm.org)
Date: 01/07/05
- Next message: Vladkoag: "RE: calculate the midle and end of the month"
- Previous message: Adam Machanic: "Re: Global Variables"
- In reply to: Gonzalo Torres: "calculate the midle and end of the month"
- Next in thread: Vladkoag: "RE: calculate the midle and end of the month"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 07 Jan 2005 21:29:30 GMT
"Gonzalo Torres" <condormix2001@yahoo.com.mx> wrote in message
news:%23kysmqP9EHA.3840@tk2msftngp13.phx.gbl...
> Hi
> I have a table with a datetime field that I use to get a date of process for
> my application.
> I have stored the dates:
> 15/01/04
>
> 31/01/04
>
> 15/02/04
>
> 29/02/04
>
> 15/03/04
>
> 31/03/04
>
> 15/04/04
>
>
> .
> .
> .
> and so on...
> This means I write the every 15th day of a month and every end of a month.
> As it's shown, 2004 it's a leap year, that's
> why the 29/02/04. But I have to check if the actual year is a leap year or
> not, so I type 29/02/04 or 28/02/04.
> As I have to type all these dates, is there a way to get them automatically?
> Let's say, the dates of 2005?
-- All mid and end of months for provided year
CREATE FUNCTION MidAndEndOfMonths (@year INT)
RETURNS TABLE
AS
RETURN(
SELECT CAST(CAST(@year AS CHAR(4)) + s + '15' AS DATETIME) AS mid_month,
DATEADD(MONTH, 1,
CAST(@year AS CHAR(4)) + s + '01') - 1 AS end_month
FROM (SELECT 1, '01' UNION ALL
SELECT 2, '02' UNION ALL
SELECT 3, '03' UNION ALL
SELECT 4, '04' UNION ALL
SELECT 5, '05' UNION ALL
SELECT 6, '06' UNION ALL
SELECT 7, '07' UNION ALL
SELECT 8, '08' UNION ALL
SELECT 9, '09' UNION ALL
SELECT 10, '10' UNION ALL
SELECT 11, '11' UNION ALL
SELECT 12, '12') AS M(m, s)
)
SELECT mid_month, end_month
FROM MidAndEndOfMonths(2005)
ORDER BY mid_month
mid_month end_month
2005-01-15 00:00:00.000 2005-01-31 00:00:00.000
2005-02-15 00:00:00.000 2005-02-28 00:00:00.000
2005-03-15 00:00:00.000 2005-03-31 00:00:00.000
2005-04-15 00:00:00.000 2005-04-30 00:00:00.000
2005-05-15 00:00:00.000 2005-05-31 00:00:00.000
2005-06-15 00:00:00.000 2005-06-30 00:00:00.000
2005-07-15 00:00:00.000 2005-07-31 00:00:00.000
2005-08-15 00:00:00.000 2005-08-31 00:00:00.000
2005-09-15 00:00:00.000 2005-09-30 00:00:00.000
2005-10-15 00:00:00.000 2005-10-31 00:00:00.000
2005-11-15 00:00:00.000 2005-11-30 00:00:00.000
2005-12-15 00:00:00.000 2005-12-31 00:00:00.000
-- JAG
- Next message: Vladkoag: "RE: calculate the midle and end of the month"
- Previous message: Adam Machanic: "Re: Global Variables"
- In reply to: Gonzalo Torres: "calculate the midle and end of the month"
- Next in thread: Vladkoag: "RE: calculate the midle and end of the month"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
