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] -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I've formatted as a date but the error i get is as below [Microsoft][ODBC SQL SQL Server Driver] Conversion failed when converting date and/or time from character string I've formatted though which is the strange thing

Time in the Database is HH:MM:SS But The issue I find in excel is that it puts it with a PM which the system wont recognise

I have written a SQL Query for my Database at work in Excel so that I can automatically update it. Took me a while to figure out the date sections and getting it to return just the current date with the below code. WHERE f.FolioStatusId in (1, 7, 8, 9, 13) AND cast(f.made_on as date) >= cast(getdate() as date) What I'd like to do is to have a section in Excel where I can type the Dates that I need the data from and too. I know that I can insert a "?" in the query section to make a cell reference but cant figure out what code would need to go in the WHERE section to do that. Any help would be greatly appreciated!