SQL中的Date函数问题 [英] Problem With Date function in SQL
问题描述
大家好,
我对SQL中的日期功能有疑问。
假设这是2013年1月。
现在,我想显示上个月的数量。
我写的查询是这样的,但这在技术上是错误的,在某些情况下这种情况是失败的。
如何获得上个月(2012年12月)的详细信息。
CASE WHEN MONTH(a.Planned_Date)=(MONTH(GETDATE()) - 1) AND ((年(a.Planned_date)=年(GETDATE()))或(年(a.Planned_date) =年(GETDATE()) - 1))
那么 a.Planned_Material_qty ELSE 0 END AS Prev_Month
可以有人给我一些建议超出此...
查看sql
选择 getdate()
将返回当前日期时间
示例:2013-02-26 23:18:57.660
选择 DateAdd(mm,-1,getdate())
将返回当前日期时间的前一个月。例如:2013-01-26 23:18:57.660
我希望你现在可以解决你的问题。
嗨Naveen
您可以使用以下功能获取上个月...
DECLARE @Input AS DATETIME
SET @Input = NULL;
WITH CurrentDate AS
(
SELECT ThisMonthFirstDay = DATEADD(MM,DATEDIFF(MM,0,ISNULL(@ Input,GETDATE())),0)
)
,数字(N)AS
(
SELECT -1 - 预测月数
- 保证金0 - 过去一个月
- 联盟所有选择-1
--UNION ALL SELECT -2
)
SELECT SlNo = CrsApp.N + 3
,[Month] = DATENAME(M,CrsApp.StartDates)+''''+ CAST(YEAR) (CrsApp.StartDates)AS VARCHAR(4))
,StartDate = CONVERT(VARCHAR(10),CrsApp.StartDates,120)
,EndDate = CONVERT(VARCHAR(10),DATEADD(DD, - ) 1,DATEADD(MM,1,CrsApp.StartDates)),120)
FROM CurrentDate CD
CROSS APPLY(
SELECT N,DATEADD(MM,NN,CD.ThisMonthFirstDay)
FROM Numbers N
)CrsApp(N,StartDates)
< pre lang = SQL>
问候
威灵顿
这是实现这一目标的一种非常简单的方法: Source [ ^ ]
SELECT DATEADD(MONTH,DATEDIFF(月, 0 ,GETDATE()) - 1 , 0 )
Hi everybody,
I have a doubt in Date function in SQL.
Assuming this is the month of Jan 2013.
Now, i want to display the previous month quantity.
for that i wrote the Query is like this, but this is technically wrong in some cases this condition is failed.
How to get previous month (Dec 2012) details.
CASE WHEN MONTH(a.Planned_Date)=(MONTH(GETDATE())-1) AND ((YEAR(a.Planned_date)=YEAR(GETDATE())) OR (YEAR(a.Planned_date)=YEAR(GETDATE())-1))
THEN a.Planned_Material_qty ELSE 0 END AS Prev_Month
can anybody give me some suggestion out of this...
see the sql
select getdate()
will return current datetime
example: 2013-02-26 23:18:57.660
select DateAdd(mm,-1,getdate())
will return previous month of current datetime. example:2013-01-26 23:18:57.660
I hope you can solve your problem now.
Hi Naveen
You can use the below function to get previous month...
DECLARE @Input AS DATETIME SET @Input = NULL; WITH CurrentDate AS ( SELECT ThisMonthFirstDay = DATEADD( MM, DATEDIFF(MM,0,ISNULL(@Input,GETDATE())) ,0) ) , Numbers(N) AS ( SELECT -1 --PREVIOUS MONTH --SELECT 0 --CURRENT MONTH --UNION ALL SELECT -1 --UNION ALL SELECT -2 ) SELECT SlNo = CrsApp.N + 3 ,[Month] = DATENAME(M, CrsApp.StartDates) + '' '' + CAST(YEAR(CrsApp.StartDates) AS VARCHAR(4)) ,StartDate = CONVERT(VARCHAR(10), CrsApp.StartDates, 120) ,EndDate = CONVERT(VARCHAR(10), DATEADD(DD,-1, DATEADD(MM,1,CrsApp.StartDates)), 120) FROM CurrentDate CD CROSS APPLY ( SELECT N , DATEADD(MM,N.N,CD.ThisMonthFirstDay) FROM Numbers N ) CrsApp(N, StartDates)<pre lang="SQL">
Regards
Willington
Here is a great and easy way to accomplish this: Source[^]
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
这篇关于SQL中的Date函数问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!