Sql Server Date Format DD-Mon-YY
We are working on porting our application from Oracle 11 to SQL Server 2014. There are many places in our Java code that expect a date formatted DD-Mon-YY
. I haven't been able to find an option with a conversion or any other built-in function to do this without overlaying the "-" into the converted string.
Today we use something like
upper((((CONVERT(NVARCHAR, Dt_Column, 113), 3, 1, '-'), 7, 3, '-'),1,18))
We are using this for multiple columns in a single view that is loading several hundred thousand rows, I suspect this might affect our performance. Any inputs / thoughts would be helpful. Thank you in advance.
source to share
This will lead to the desired effect:
SELECT REPLACE(CONVERT(NVARCHAR, Dt_Column, 106), ' ', '-')
The style 106
for CONVERT
contains the date in a format dd mon yyyy
that you can simply replace spaces with a dash.
Update
Based on the additional information that the format should also include time, you can try using FORMAT
:
SELECT FORMAT(SYSDATETIME(), 'dd-MMM-yyyy hh:mm:ss')
Keep in mind that it FORMAT
relies on the CLR, so you will need to evaluate the performance impact. Although with your dataset, one call FORMAT
can be equivalent or potentially better than multiple calls to your own functions.
In any case, if you find that the impact of getting the date in the correct format is too great, you can use a constant computed column in SQL Server to store the formatted date. If you don't want to rename the column reference in your Java code, you can rename the original column and name the new computed column with the original name of the source column.
[PERSISTED] will physically store the calculated values in the table and update the values when any other columns on which the calculated column depends are updated.
source to share
Using Joey as a starting point and combining this with the information from the Date and Time section on docs.microsoft.com CAST and CONVERT (Transact-SQL) page, CONVERT (NVARCHAR, @Value, 6) emits the two digit year you were looking for.
DECLARE @Now AS DATETIME = GETDATE()
SELECT @Now AS '@Now'
, REPLACE(CONVERT(NVARCHAR, @Now, 6), ' ', '-') AS 'Abbr@Now'
/***********************************************************************
Results:
@Now Abbr@Now
----------------------- ---------
2017-12-22 10:08:33.443 22-Dec-17
(1 row(s) affected)
***********************************************************************/
Additional DATETIME formatting using CONVERT () can be found at: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
Hope this helps.
source to share