INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Computed Field, Concatenate from Date data

Computed Field, Concatenate from Date data

(OP)
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

RE: Computed Field, Concatenate from Date data

With more recent SQL Server Versions you get it simply as

CODE

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....

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.

CODE

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:

CODE

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.

RE: Computed Field, Concatenate from Date data

(OP)
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

RE: Computed Field, Concatenate from Date data

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.

RE: Computed Field, Concatenate from Date data

(OP)
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?

RE: Computed Field, Concatenate from Date data

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.

RE: Computed Field, Concatenate from Date data

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close