DATETRUNC

• 
  SQL prompt doesn't seem to handle DATETRUNC when formatting, instead of this:
  

  SELECT DATETRUNC (YEAR, [d].[FullDate]) AS [YearStartDate] FROM [Dim].[Date] AS [d];

  
  I get this, which errors when executed:

  SELECT [DATETRUNC] ([YEAR], [d].[FullDate]) AS [YearStartDate] FROM [Dim].[Date] AS [d];

  ccparkhill October 08, 2024 09:40

  - 0 +
• 
  Hi Chris,

  
  

  This seems to be working on the latest version of Prompt (10.14.23) and for an on-prem SQL Server

  
  

  
  

  Are you seeing this on a similar configuration or is it different? If its an earlier version of Prompt, could you check if upgrading to the latest version works?

  
  

  Sujay October 11, 2024 06:31

  - 0 +
• 
  Hi Sujay,
  
  I have the latest version, I'm using it against Azure SQL, here are my settings:
  
  

  ccparkhill October 11, 2024 08:17

  - 0 +
• 
  Hi Chris,

  
  

  Thank you for that. I was able to replicate the issue with Azure SQL database. I will escalate this to the development team to look into and will let you know as soon as there are any updates.
  

  Sujay October 14, 2024 07:10

  - 0 +
• 
  Thanks Sujay, I am also seeing a similar issue when adding IGNORE NULLS to FIRST_VALUE:
  

  SELECT

      [d].[FullDate]

    , FIRST_VALUE ([d].[FullDate]) IGNORE NULLS OVER (PARTITION BY [d].[FinancialYear] ORDER BY [d].[FullDate]) AS [FinYearStartDate]

  FROM [Dim].[Date] AS [d]

  ORDER BY 1;

  
  

  SELECT

      [d].[FullDate]

    , FIRST_VALUE ([d].[FullDate]) [IGNORE] [NULLS] OVER ([PARTITION] BY [d].[FinancialYear] ORDER BY [d].[FullDate]) AS [FinYearStartDate]

  FROM [Dim].[Date] AS [d]

  ORDER BY 1;

  ccparkhill October 15, 2024 10:30

  - 0 +
• 
  Hi Chris,

  
  

  Thank you for the update.

  I have replicated this one as well on Azure SQL and have shared the details to the development team to look into.
  

  Sujay October 16, 2024 02:25

  - 0 +
• 

  Thank you for your patience whilst this was escalated with our developers.
  

  A fix has been included in release V10.15.1 of SQL Prompt
  

   

  Download link for this version can be found here:

  https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.15.1.12422.exe

   

  Release notes:

  https://documentation.red-gate.com/sp/release-notes-and-other-versions/sql-prompt-10-15-release-notes

  10.15.1.12422 - Released on 19 November 2024
  Features

  • Updates the licensing client

  Fixes

  • Allows tool to be used across user profiles on the same machine

  • Prioritises licenses correctly

  • Fixed an issue where SQL Prompt does not handle IGNORE NULLS on Azure SQL database.

  • Fixed an issue where SQL prompt does not handle DATETRUNC on Azure SQL DB.

    
    

  Jon_Kirkwood November 21, 2024 02:43

  - 0 +
• 
  Thanks, appears to be working fine.

  ccparkhill November 21, 2024 15:39

  - 0 +

Add comment

Please sign in to leave a comment.