如何使用sqlserver中的WITH子句获取当前月份和当前日期的数据 [英] how to get current month and current day of data using WITH clause in sqlserver

问题描述

我有一个查询，从当前日期返回最近7天的数据，但是当用户从前端应用程序输入日期时，我想获取当前月份的数据和当前日期的数据.在这里，我给出的查询将返回过去7天的数据.

Hi,

i have a query that returns last 7 days of data from the current date but i want to get current month data and current day of data when user enter date from front end application. here i am giving query that returns last 7 days of data .

ALTER PROCEDURE [dbo].[New_QueryTotalShipOrders_WEEKITEM]  
@DateFrom  NVARCHAR(50)
AS
DECLARE @CurrentDate NVARCHAR(10)

BEGIN
SET @CurrentDate= @DateFrom--'2012-07-27'
SET @CurrentDate=(SELECT REPLACE(@CurrentDate,'-','/'));

WITH DateList AS
(
SELECT  DATEADD(dd, 0, CONVERT(DATETIME, @CurrentDate)) AS CreateDateTime, 1 AS Cnter
UNION ALL
SELECT  DATEADD(dd, -1, CreateDateTime), DateList.Cnter + 1
FROM DateList
WHERE DateList.Cnter  < 7
)
SELECT  DateList.CreateDateTime AS ShipDay, COALESCE(Temp.TotalCount, 0) AS TotalCount, 'Consolidate' as ConsolidateCount
FROM DateList
LEFT JOIN  (
SELECT  COUNT(Id) TotalCount
,CONVERT(VARCHAR(10), CreatedDateTime, 112) AS CreateDateTime
FROM    ShipmentDetail sd
Inner JOin(select Distinct shipmentId,Sku From items) as i on i.ShipmentID=sd.Id
WHERE   CreatedDateTime >= DATEADD(dd, -6, @CurrentDate)
AND CreatedDateTime < DATEADD(dd, 1, @CurrentDate) 
and sd.Id IN (SELECT i1.ShipmentID

 FROM Items i1 (NOLOCK)
 WHERE i1.SubOrderId = sd.SubOrderId  and i.Sku in('2x3SGC','3X5SNC','4x5SGC','ADDRLBL','STICKER01'))

GROUP BY CONVERT(VARCHAR(10), CreatedDateTime, 112)
) Temp
ON CONVERT(VARCHAR(10), DateList.CreateDateTime, 112) = Temp.CreateDateTime

union all

SELECT  DateList.CreateDateTime AS ShipDay, COALESCE(Temp.TotalCount, 0) AS TotalCount,'NonConsolidate' as NonConsolidateCount
FROM DateList
LEFT JOIN  (
SELECT  COUNT(Id) TotalCount
,CONVERT(VARCHAR(10), CreatedDateTime, 112) AS CreateDateTime
FROM    ShipmentDetail sd
Inner JOin(select Distinct shipmentId,Sku From items) as i on i.ShipmentID=sd.Id
WHERE   CreatedDateTime >= DATEADD(dd, -6, @CurrentDate)
AND CreatedDateTime < DATEADD(dd, 1, @CurrentDate)
and sd.Id IN (SELECT i1.ShipmentID

 FROM Items i1 (NOLOCK)
 WHERE i1.SubOrderId = sd.SubOrderId  and i.Sku in('NOTEBOOK01','NOTEBOOK02','NOTEPAD01','5X7SNP','CALENDAR01','CALENDAR02'))

GROUP BY CONVERT(VARCHAR(10), CreatedDateTime, 112)
) Temp
ON CONVERT(VARCHAR(10), DateList.CreateDateTime, 112) = Temp.CreateDateTime

END

推荐答案

尝试:

Try:

SELECT left(convert(varchar, getdate(), 110),2) as Month ,left(convert(varchar, getdate(), 103),2) as Day

这篇关于如何使用sqlserver中的WITH子句获取当前月份和当前日期的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！