如何检索当前月份日期值

本文介绍了如何检索当前月份日期值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我编写了一个查询来从开始日期和结束日期检索当前月份值。并且我正在获得当前月份和明年的价值也会返回

这是我的查询

I have written a query to retrieve current month value from start date and end date. and am getting current month as well as next year value also returning

this is my query

SELECT 
    PaymentTracker1.*
    , convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate
    ,(Numbers.N * MonthlyPay) AS DueAmount
FROM 
    PaymentTracker1 
    INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N 
    
    where  datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm,getdate()) and  datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate())
ORDER BY
    PaymentTracker1.Name
    ,Numbers.N

查询的输出图片点击下方链接

http://i.stack.imgur.com/LMg1m.png

明年也会获得月值，

如何仅返回当前月份和年份值

the output image of query is click below link

http://i.stack.imgur.com/LMg1m.png

am getting next year month value also,

how to return only current month and year value

推荐答案

DECLARE @mydate DATETIME, @starDate datetime, @EndDate datetime
SELECT @mydate = GETDATE()

set @starDate = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101))

set @EndDate =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101))


SELECT
    PaymentTracker1.*
    , convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate
    ,(Numbers.N * MonthlyPay) AS DueAmount
FROM
    PaymentTracker1
    INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N

    where  StartDate>=@starDate and StartDate<@EndDate
ORDER BY
    PaymentTracker1.Name
    ,Numbers.N

这篇关于如何检索当前月份日期值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！