Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7474
  • Last Modified:

Subtracting 1 month from date and formatting to (mmm-yy) i.e.APR-06

I have a date that I'm inserting into a table 'FRA' and I want to also include another column called 'REPORT_DATE' in the table besides the timestamp called 'INSERT_DATE'.
Here is what 'INSERT_DATE' looks like 2006-04-28 13:39:09.577.  I want to take this date and subtract 1 month from it and format in the following way: mmm-yy.  An example of the formated date for 2006-04-28 13:39:09.577 would be APR-06, but I also want to subtract one month so it would really be "MAR-06" based on the 2006-04-28 13:39:09.577.  thanks.
 
0
fusionfx2000
Asked:
fusionfx2000
1 Solution
 
bruintjeCommented:
Hello fusionfx2000,

you could use something like

SELECT UPPER(CONVERT(CHAR(3), DATEADD(month, -1, INSERT_DATE) , 107)) + '-' + CAST(YEAR(INSERT_DATE) AS CHAR)

hope this helps a bit
bruintje
0
 
rafranciscoCommented:
To format your date as APR-06, you can do the following:

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]

This was taken from the following link:

http://www.sql-server-helper.com/tips/date-formats.aspx

To subtract one month, you will use the DATEADD date function.  You can refer to question #17 in the following link:

http://www.sql-server-helper.com/faq/dates-p02.aspx
0
 
imran_fastCommented:
want to subtract month from it.
====================

SELECT UPPER(REPLACE(RIGHT(CONVERT(VARCHAR(9), DATEADD(MONTH,-1,'2006-04-28 13:39:09.577'), 6), 6), ' ', '-') )
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now