Format function doesn't seem to work on date column

Transact-SQL
1

I have a column that returns as:
DATEADD(DAY, 1, EOMONTH(MyDate, -1)) AS [REPORTING PERIOD]

that gets input into a table per Date column of same name [REPORTING PERIOD]

when i use the below, it doesn't work:
select FORMAT([REPORTING PERIOD],'yyyyMM'), * from MyTable

Weirdly, it returns: 202301

Note that when looking at the column in MyTable, it shows 2023-01-01 for example. As mentioned MyTable is a created permanent table which has the REPORTING PERIOD as data type: DATE

It returns correctly when I use:
Declare @Date DateTime = '10/16/2015'
Select Format(@Date, N'MMMM-yyyy')

Any help on what I'm doing incorrectly is appreciated!

2

I'm not sure I understand the confusion:

Declare @Date DateTime = '10/16/2015'
Select Format(@Date, N'MMMM-yyyy'), FORMAT(@Date,'yyyyMM')

You're using 2 different formats, they will output different strings for the same date.

1 Like
3

Sorry, you are correct, I hadn't noticed that. Can i blame on lack of sleep? :wink:
It works now..

4

so, it worked, but the whole reason I need this is that the requester is asking to see the date as the whole month name plus the year

For example, from 07/01/2023 to the format: July-2023. The format function handles this, but 'July-2023' will not update into the Date Field, not by itself nor when I try to to enclose it in a conversion function.

So, this works: SELECT CONVERT(DATETIME,'12/13/2019',103)

But if I substitute the date with the desired formatting it does not work:
SELECT CONVERT(DATETIME,'December-2023',103)

Can this just not be done or am I missing something?

Thanks for any continued help!

5

First, I'm going to strongly suggest that you STOP USING FORMAT. Please see the following article for why.

Here's the way to convert the current date and time to what you want. Of course, make the appropriate substitution to replace GETDATE() with your DATETIME or DATE column and add a proper FROM clause.

 SELECT CONCAT(DATENAME(mm,GETDATE()),'-',DATENAME(yy,GETDATE()))
;
6

Thanks Jeff. Believe me, I don't want to use Format or any kind of date conversion. My customer has insisted on a particular output format for the date.

I think to resolve this I will need to add a new varchar column to my table, place that format in this column and then sort by the date column per the output.

Thanks for all your help!

7

Wouldn't it be easier to teach the customer how to do the display conversion instead of having to add another column to the table? Or, maybe provide them with an iTVF (Inline Table Valued Function) that does the conversion for them?

1 Like
8

If you absolutely must include such a column, understand that neither DATENAME() nor FORMAT() can be used as an index-able PERSISTED computed column.

Also understand that manually maintaining such columns is futile and expensive.

If the column must be VARCHAR instead of CHAR, create the column as a VARCHAR(14) and default it to SPACE(14) so that the upcoming modification does NOT constitute an "ExpAnsive Update", which would cause page-splits (which are hugely expensive) and the resulting fragmentation.

Then, create a trigger for INSERT and UPDATEs to the normal date column that will update the display column using the formula of...

CONCAT(DATENAME(mm,xxxxxx),'-',DATENAME(yy,xxxxxx))

... where the xxxxxx is the name of the normal date column. Do NOT use FORMAT!!! It will make everything having to do with INSERTs and UPDATEs to the normal date column more than 20 times slower.

I'll also say,again, that storing formatted dates is a mortal sin in the "Good Book of Boy Howdy" when it comes to SQL and it would be MUCH better to teach the customer how to do it correctly.

9

I am going to recommend that you stop trying to create a column in a specific 'date' format. Either create that column in the SELECT statement being used to output the results to the reporting system - or better yet, use the style formatting in the reporting system.

For example - if using SSRS as the reporting system then format the field in SSRS to be whatever format you want to display to the customer. The query returns a DATE or DATETIME column and SSRS formats the field appropriately.

1 Like