Solved

How do I convert this combination into a specific date

Posted on 2011-02-18
4
255 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 250 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

740 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