Where to place FORMAT in Query

[rhysdunn]

Good Morning,

 

I've put together a Query together to gather some Data that I can run on Microsoft Excel and use the data in a pivot table. What I am finding though is that when I reference the pivot table for the "Date" Column on a separate work sheet (Date format is DD/MM/YYYY on the sheet, but displays as YYYY-MM-DD in the pivot Table) I constantly get a REF error. Would It be best to place a Format command in the Query or should I be looking somewhere else to fix this error? I've attached the code below and any help would be appreciated!

 

SET NOCOUNT ON

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @DATES datetime

DECLARE @DATEE datetime

SET @DATES = CONVERT(DATE,GETDATE()-40)

SET @DATEE = CONVERT(DATE,GETDATE()-1)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE [MSADailyStats](

[Date] [date],

[Rooms] [integer],

[Revenue] [money],

[OOO] [integer])

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO [MSADailyStats]

([Date],[Rooms])

SELECT [stats_Date],SUM([Nights]) AS [Nights]

FROM [Daily_Statistics]

WHERE [stats_Date] >= @DATES AND [stats_Date] <= @DATEE

GROUP BY [stats_Date]

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MERGE INTO [MSADailyStats] [MSA]

USING

(

SELECT [stats_Date],SUM([Trans_Value]) AS [Trans_Value]

FROM [Transaction_Statistics]

WHERE [stats_Date] >= @DATES AND [stats_Date] <= @DATEE AND [TransCodeID] IN (9000149,9000156,9000151,9000152,9000153,9000156,9000181,9000182,9000183,9000184,9000185,9000186)

GROUP BY [stats_Date]

) [CMS]

ON [MSA].[DATE] = [CMS].[stats_Date]

WHEN MATCHED THEN

UPDATE SET [Revenue] = [CMS].[Trans_Value];

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MERGE INTO [MSADailyStats] [MSA]

USING

(

SELECT [booked_Date],COUNT([RoomID]) AS [RoomID]

FROM [Rooms_Booked]

WHERE [OOOId] > 1 AND [booked_Date] >= @DATES AND [booked_Date] <= @DATEE

GROUP BY [booked_Date]

) [CMS]

ON [MSA].[DATE] = [CMS].[booked_Date]

WHEN MATCHED THEN

UPDATE SET [OOO] = [CMS].[RoomID];

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT *

FROM [MSADailyStats]

ORDER BY [Date]

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DROP TABLE [MSADailyStats]

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[justsomeguy]

Can you reference the other columns in the table? Date might be a reserved word, but if you use it in brackets then I think it should work.