Kamis, 13 Oktober 2011
Cetak Tanggal Dalam Sebulan
declare @month int, @year int
set @month = 2
set @year = 2009
SELECT
CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) + number
FROM master..spt_values
WHERE type = 'P'
AND (CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) + number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) )
set @month = 2
set @year = 2009
SELECT
CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) + number
FROM master..spt_values
WHERE type = 'P'
AND (CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) + number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) )
Rabu, 12 Oktober 2011
Hari minggu
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fget_sunday_of_year](@year int)
RETURNS @tabel TABLE (date datetime)
AS
BEGIN
DECLARE @date datetime, @n int, @i int;
SET @i=1;
SET @date=(SELECT cast(year(cast(@year as varchar(4))) as varchar(4))+'-12-31');
SET @n=(SELECT DATEPART(wk, @date));
SET @date=(SELECT DATEADD(DAY, (7 - DATEDIFF(DAY, '17530101', DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), '19000101')) % 7) % 7, DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), '19000101'))-1 AS date)
INSERT @tabel
SELECT @date
WHILE(@i<@n-1)
BEGIN
SET @date=(SELECT @date+7);
INSERT @tabel
SELECT @date;
SET @i=@i+1;
END
RETURN;
END;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fget_sunday_of_year](@year int)
RETURNS @tabel TABLE (date datetime)
AS
BEGIN
DECLARE @date datetime, @n int, @i int;
SET @i=1;
SET @date=(SELECT cast(year(cast(@year as varchar(4))) as varchar(4))+'-12-31');
SET @n=(SELECT DATEPART(wk, @date));
SET @date=(SELECT DATEADD(DAY, (7 - DATEDIFF(DAY, '17530101', DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), '19000101')) % 7) % 7, DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), '19000101'))-1 AS date)
INSERT @tabel
SELECT @date
WHILE(@i<@n-1)
BEGIN
SET @date=(SELECT @date+7);
INSERT @tabel
SELECT @date;
SET @i=@i+1;
END
RETURN;
END;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
Langganan:
Postingan (Atom)