|
Subject:
|
DDD-YYYY
|
|
Posted By:
|
carumuga
|
Post Date:
|
1/19/2007 1:20:10 AM
|
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
|
|
Reply By:
|
sshelper
|
Reply Date:
|
1/23/2007 2:15:47 PM
|
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
|
|
Reply By:
|
agossage
|
Reply Date:
|
1/24/2007 11:34:02 AM
|
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
|
|
Reply By:
|
sshelper
|
Reply Date:
|
1/25/2007 9:58:25 AM
|
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
|
|
Reply By:
|
agossage
|
Reply Date:
|
1/25/2007 10:19:00 AM
|
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
|
