How do I convert this combination into a specific date

I have a fiscal years (ranging from 2005 - 2011) and various dates as of 6/30/XX.  I need to change the 6/30/XX into the proper year based on the fiscal year.  If the fiscal year is 2005 I need the date to be 6/30/04.  If the fiscal year is 2006 I need the date to be 6/30/05.   I can't quite figure out how to do this
cindyfillerAsked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
Does the following work for you?

select CAST(CAST(2005 - 1 AS varchar(10)) + '-06-30' AS DATETIME)

0
 
tigin44Commented:
try this
SELECT dateColumn, DATEADD(year,  -1 * (YEAR(dateColumn) - fiscalYearColumn),  dateColumn)  
FROM yourTable

Open in new window

0
 
lcohanDatabase AnalystCommented:
I guess the CAST/CONVERT SQL functions is what you need:

http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx
0
 
dxdinhCommented:
I am sure I understand the question but wonder if something like this will work ?

declare @yy AS INT
declare @date as dateTime

-- get the current fiscal year - 1
Set @yy = (YEAR(GETDATE()) % 100) - 1

-- set your date
Set @date = '6/30/' + CAST (@yy AS VARCHAR (100))

Print @date
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.