|
--DROP FUNCTION TABLE_MONTH_INFO();
CREATE FUNCTION TABLE_MONTH_INFO ( @CurrentDate Char(10) )
RETURNS @tblMONTHINFO Table
(
CurrentDate Char(10),
MSTART Char(10),
MEND Char(10),
MSTARTDAY integer,
MENDDAY integer
)
--/////// {
AS BEGIN
DECLARE @MSTART as Char(10)
DECLARE @MEND_TEMP as Varchar(30)
DECLARE @MEND as Char(10)
DECLARE @MSTARTDAY as integer
DECLARE @MENDDAY as integer
SELECT @MSTART = LEFT(@CurrentDate, 4) + '-'
+Substring(@CurrentDate, 6,2) + '-01'
SELECT @MEND_TEMP = DATEADD(day, -1, DATEADD(mm, 1,
@MSTART))
SELECT @MEND = CAST(DATENAME(yyyy, @MEND_TEMP) as Char(4)) + '-' +
CAST(DATENAME(mm, @MEND_TEMP) as Char(2)) + '-' + CAST(DATENAME(dd,
@MEND_TEMP) as Char(2))
SELECT @MSTARTDAY = CAST(RIGHT(@MSTART, 2) AS Integer)
SELECT @MENDDAY = CAST(RIGHT(@MEND, 2) AS Integer)
INSERT @tblMONTHINFO (CurrentDate, MSTART, MEND, MSTARTDAY,
MENDDAY) VALUES ( @CurrentDate, @MSTART, @MEND, @MSTARTDAY,
@MENDDAY)
RETURN
END
--/////// }
==========================================================================
쿼리 : SELECT * FROM
[dbname].[dbo].[TABLE_MONTH_INFO]('2004-06-25')
결과 :
CurrentDate | MSTART| MEND | MSTARTDAY | MENDDAY
2004-06-25 | 2004-06-01 | 2004-06-30 | 1 | 30
==========================================================================
더 간단하게 만들수 있으면 한수부탁합니다...!!!
|
