• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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
0
cindyfiller
Asked:
cindyfiller
1 Solution
 
wdosanjosCommented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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