DATETRUNC

ccparkhill

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

Jon_Kirkwood

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.

  
  

ccparkhill

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

Sujay

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?

ccparkhill

Hi Sujay,

I have the latest version, I'm using it against Azure SQL, here are my settings:

Sujay

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.

ccparkhill

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;

Sujay

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.

ccparkhill

Thanks, appears to be working fine.