[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do I convert this combination into a specific date

Posted on 2011-02-18
4
Medium Priority
?
279 Views
Last Modified: 2012-05-11
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
Comment
Question by:cindyfiller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 1000 total points
ID: 34929976
Does the following work for you?

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

0
 
LVL 26

Expert Comment

by:tigin44
ID: 34929978
try this
SELECT dateColumn, DATEADD(year,  -1 * (YEAR(dateColumn) - fiscalYearColumn),  dateColumn)  
FROM yourTable

Open in new window

0
 
LVL 40

Expert Comment

by:lcohan
ID: 34929988
I guess the CAST/CONVERT SQL functions is what you need:

http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx
0
 
LVL 7

Expert Comment

by:dxdinh
ID: 34930073
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question