Hi,

I have Date in format DDD-YYYY (varchar field in the database)
The valid dates are 010-2005, 100-1998, ...
DDD can be between 1 and 365 and YYYY can be between 1900 and 2010.

Is there a way to convert this format to DDMMYYYY or MMDDYYYY or any other valid formats which SQL SERVER understand, so that i can make use of the function ISDATE()

Thanks in advance



Chandru

__________________
Thanks,
Chandra

Try the following:

DECLARE @DDD_YYYY VARCHAR(8)
SET @DDD_YYYY = '010-2007'
SELECT CAST(RIGHT(@DDD_YYYY, 4) + '/01/01' AS DATETIME) + CAST(LEFT(@DDD_YYYY, 3) AS INT)

For other SQL Server Date Formats:
http://www.sql-server-helper.com/tips/date-formats.aspx

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

You can parse the string into days and year. Set the date to 1/1/YYYY for the given year. Then use DATEADD to add the total days. Then you can format the date however you like with CONVERT.

Example:
DECLARE @str varchar(8)
SET @str = '089-2001'
SELECT DATEADD(d, CONVERT(smallint, SUBSTRING(@str, 1, 3)), CONVERT(datetime, '1/1/' + SUBSTRING(@str, 5, 4)))


Adam Gossage
Lake Wylie, SC, USA

If you look at the code that I gave and compare it to the code agossage provided, it provides the same result. My script returns the string in datetime function. I just didn't use the DATEADD function because using the + basically does the same thing, add a number of days to the date. Try my code and let me know if it doesn't give the same result.

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

You're right, I just gave another method. I think the calculation is wrong in both cases.

The days portion of the string is the day of the year. If we start at the 1st day, our calculation is one day off. Since 1/0/YYYY is not a valid date, we would have to subtract 1.

Please correct me if I am wrong.

Correct Example:
DECLARE @str varchar(8)
SET @str = '010-2001'
SELECT DATEADD(d, CONVERT(smallint, SUBSTRING(@str, 1, 3)) - 1, CONVERT(datetime, '1/1/' + SUBSTRING(@str, 5, 4)))


Adam Gossage
Lake Wylie, SC, USA