Computed Field, Concatenate from Date data 1

[Halliarse]

I have a table, Invoice that has a column InvoiceDate, type date. I need to create a computed field that is based on the month number and the year number of the InvoiceDate but it needs to be in the format MMYY.
For eample, if my InvoiceDate is 2014-07-14, I need the result to be 0714.

I've tried all sorts of combinations using the Month and Year functions, Cast and Substring but cannot acheive my requirement.

Can anyone please offer some advise or point me in the right direction?

Many thanks

Steve

 

[Olaf Doschke]

With more recent SQL Server Versions you get it simply as

Select Format(GetDate(),'MMyy')

If that won't work look into the CONVERT function, not CAST. It's described within the same help topic:

https://msdn.microsoft.com/en-us/library/ms187928.aspx

Looking for a format containing MMYY in this order with two digit year, the best fits have MM/YY, MM.YY, or MM-YY, with the third style parameter being 3,4,5 with different separators.
You need a substring from 4th position to 8th and then remove the slash or dot or dash separator with REPLACE.

Select REPLACE(RIGHT(CONVERT(CHAR(8), GetDate(), 3), 5),'/','')

Obviously specify your datetime column InvoiceDate instead of GetDate(). And you see it pays to have a more recent SQL Server version running, things like formatting dates get easier. There's also EOMONTH (end of month date), DATEFROMPARTS, DATETIMEFROMPARTS and other helpful functions putting those "well known" yet asked over again expressions for certain specific days or formatting to a rest.

Bye, Olaf.

PS: Just to see how to build up such expressions in slow motion one function after the other:

Select GetDate()
Select CONVERT(CHAR(8), GetDate(), 3)
Select RIGHT(CONVERT(CHAR(8), GetDate(), 3), 5) 
Select REPLACE(RIGHT(CONVERT(CHAR(8), GetDate(), 3), 5),'/','')

Showing the stages from the mere datetime to certain style (3 = british or french), just the right 5 chars of that and finally the slash removed.

 

[Halliarse]

Hi Olaf, thanks for the response.

If I'm creating this as a Computed Column, what is the correct formula? I am getting an error that 'Format' is not a recognized built in function.

Thanks

Steve

 

[Olaf Doschke]

Besides all that you can typically get a date or datetime from SQL Server 1:1 as is and format it with the frontend/report engine etc. It's actually not the task of a database to format data.

Bye, Olaf.

 

[Halliarse]

Ok, I'll explain my reasoning for needing this. I have a database that has 3 tables, Sales, Purchase and Costs. Each of these tables has a date field and I need to be able to link them all to each other based on an accounting period, as per my initial question, say 0714. I can then create a single Crystal report that groups all of the data by accounting period. Does that make sense?

 

[Olaf Doschke]

Please just rearead my initial post: Format is only avaliable with recent SQL Server versions.

I said: "If that won't work look into the CONVERT function, not CAST."

You can execute all my code in a session of MSSQL Management Studio and then see which is giving you the MMYY, the longest of all, the final expression.

In regard of your reasoning to need this for referencing/connecting data from Sales, Purchase and Costs you make a huge error, if you join by such a column, you cross join many records not belonging to each other. You can use this for filtering, but then don't need to store this as computed column in the table. You also don't need that specific format. You rather would GROUP BY Year(InvoiceDate), Month(InvoiceDate).

Bye, Olaf.

 

[Halliarse]

Apologies Olaf, I overlooked that part of your response....it works perfectly! Many thanks