Sql Server Date Format DD-Mon-YY

Question

We're working to migrate our app from Oracle 11 to SQL Server 2014. There are lot of places in our Java code that expects the date in DD-Mon-YY format. I couldn't find an option with convert or any other built-in function to do this with out stuffing "-" in to the converted string.

Today we are using something like

upper((((CONVERT(NVARCHAR, Dt_Column, 113), 3, 1, '-'), 7, 3, '-'),1,18))

We are using this for multiple columns in the same view that loads a few hundred thousand rows, I'm suspecting it might affect our performance. Any inputs/thoughts will be helpful. Thank you in advance.

Answer 1

This will achieve the desired affect:

SELECT REPLACE(CONVERT(NVARCHAR, Dt_Column, 106), ' ', '-')

Style 106 for CONVERT provides the date in the format dd mon yyyy, which you may then simply replace the spaces with the dash.

Update

Based on the additional information that the format should also include time, you could try using FORMAT:

SELECT FORMAT(SYSDATETIME(), 'dd-MMM-yyyy hh:mm:ss')

One thing to keep in mind is that FORMAT relies on the CLR, so you would need to gauge the performance impact. Although, with your dataset a single call to FORMAT could be equivalent or potentially perform better than multiple native function calls.

In either case, if you find the impact of obtaining the date in the correct format is too great, you could use a persisted computed column in SQL Server to hold the formatted date. If you did not want to rename the column reference in your Java code, you could rename the source column and name the new computed column the original name of the source column.

See https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql.

[PERSISTED] will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated