问题描述
使用 SQL Server 2000
Using SQL Server 2000
我想按月保存详细信息
用户输入
ID = 001
Fromdate = 01/01/2012
Todate = 29/03/2012
ID = 002
Fromdate = 01/05/2012
Todate = 19/06/2012
ID = 003
Fromdate = 01/04/2012
Todate = 30/04/2012
.....
从用户条目中,我想将详细信息插入到 table1 中,条件是 fromdate 和 todate 拆分为每月 (mm/yyyy)
From the user entry, i want to insert the details in to table1 with the condition like fromdate and todate split in to monthwise (mm/yyyy)
预期输出
ID period fromdate todate
001 01/2012 01/01/2012 31/01/2012
001 02/2012 01/02/2012 29/02/2012
001 03/2012 01/03/2012 29/03/2012
002 05/2012 01/05/2012 31/05/2012
002 05/2012 01/06/2012 19/06/2012
003 04/2012 01/04/2012 30/04/2012
....
....
fromdate,todate 格式为 dd/mm/yyyy
句点格式为mm/yyyy
fromdate, todate fomat is dd/mm/yyyy
period format is mm/yyyy
如何在 sql server 中执行此操作.
How to do this in sql server.
需要sql查询帮助
推荐答案
首先,创建并填充一个 日历表:
First, create and populate a calendar table that has the following columns (at least):
CREATE TABLE dbo.Calendar (
BaseDate datetime NOT NULL PRIMARY KEY,
Period char(7) NOT NULL,
YearNumber int NOT NULL,
MonthNumber int NOT NULL,
IsFirstDayOfMonth bit NOT NULL,
IsLastDayOfMonth bit NOT NULL
)
然后此查询将返回您需要的数据,对于一对 fromdate
和 todate
但您当然可以将其放入存储过程并每次调用一次用户输入或您从应用程序执行 SQL 的任何方式:
Then this query will return the data you need, for one pair of fromdate
and todate
but you can of course put it into a stored procedure and call it once per user input or whatever way you execute SQL from your application:
declare @ID char(3), @FromDate datetime, @ToDate datetime
select @ID ='001', @FromDate = '20120107', @ToDate = '20120917'
select
@ID as 'ID',
c.Period,
case when c.IsFirstDayOfMonth = 0x0 then @FromDate else c.BaseDate end as 'FromDate',
case when @ToDate < c1.BaseDate then @ToDate else c1.BaseDate end as 'ToDate'
from
dbo.Calendar c
join dbo.Calendar c1
on c.YearNumber = c1.YearNumber and c.MonthNumber = c1.MonthNumber
where
c.BaseDate between @FromDate and @ToDate and
(c.IsFirstDayOfMonth = 0x1 or c.BaseDate = @FromDate) and
c1.IsLastDayOfMonth = 0x1
这篇关于如何将日期拆分为每月的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!