To add year to dd/mm data field where the data type is text format 1

[varadha72]

Hi,

Can someone help me here.
Table- dbo.Account
Field Name- FiscalEndDate
Datatype- Varchar (20)
sample value-
FiscalEndDate
8/30
6/5
10/20

Logic- check the 'mm' and 'dd' in the text field "FiscalEndDate" with current system date and
a) if month of system day and month are less than the day and month of FiscalEndDate then add previous year to mm/yy and convert the format as mm/dd/yyyy.
b) if month of system day and month are less than the day and month of FiscalEndDate then add current year to mm/yy and convert the format as mm/dd/yyyy.
c) if both are same then add 2014

Sample Output-
08/30/2013
06/05/2013
10/20/2014

Please someone help me here.

 

[fredericofonseca]

add current year to string and convert to date, then compare to the current date and perform operations as required.
Just make sure your logic is correct, and read the manual for required datetime functions and convert function.

this should point you in the right direction - after you tried and if your code is giving errors please fell free to ask for further help

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[SQLBill]

So you will never have a FiscalEndDate for this year if it isn't October? In other words, there is no way 8/30 could be from this year, even though that was less than two months ago?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[varadha72]

Thanks it worked. I used,

SELECT CASE
WHEN CONVERT(datetime,(FiscalEndDate +'/2014'),111)< GETDATE()
THEN CONVERT(datetime,(FiscalEndDate +'/2013'),111)
WHEN CONVERT(datetime,(FiscalEndDate +'/2014'),111)> GETDATE()
THEN CONVERT(datetime,(FiscalEndDate +'/2014'),111)
ELSE CONVERT(datetime,(FiscalEndDate +'/2014'),111)
END FISCAL_YEAR
FROM ACCOUNT

 

[bborissov]

Make it simple:

SELECT CASE WHEN CONVERT(datetime,(FiscalEndDate +'/2014'),111)< GETDATE()
                 THEN CONVERT(datetime,(FiscalEndDate +'/2013'),111)
            ELSE CONVERT(datetime,(FiscalEndDate +'/2014'),111)
        END FISCAL_YEAR
FROM ACCOUNT

Borislav Borissov
VFP9 SP2, SQL Server