cast 7 digit string format date into date in sql

Question

hi guyes, my requirement is cast the '2013050' date into date in sql please help

Vulpes · Answer

In the ccyyddd format: cc represents the century yy represents the year in that century (0 to 99) ddd represents the day in that year (1 to 365) or (1 to 366 in a leap year) As Jignesh said, that's not a supported format in SQL Server. However, it's fairly easy to convert it into a 'normal' date in C# code if that's any help to you: using System; class Test { static void Main() { string ccyyddd = "2013050"; DateTime dt = FromCCYYDDD(ccyyddd); Console.WriteLine(dt.ToString("dd/MM/yyyy")); // 19/02/2013 Console.ReadKey(); } static DateTime FromCCYYDDD(string ccyyddd) { int[] days = {0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334, 365}; int year = int.Parse(ccyyddd.Substring(0, 4)); int ddd = int.Parse(ccyyddd.Substring(4)); if (DateTime.IsLeapYear(year)) { if (ddd == 60) return new DateTime(year, 2, 29); if (ddd > 60) ddd--; } int month = 0; for(int i = 1; i { if (ddd { month = i; break; } } return new DateTime(year, month, ddd - days[month - 1]); } }

Jignesh Trivedi · Answer

yes Vulpes it must Declare @value varchar(7) = '2013050' select DATEADD(d,CAST(SUBSTRING(@value,5,3) as int) - 1, cast(SUBSTRING(@value,1,4) + '-01-01' as date)) thx.

Jignesh Trivedi · Answer

hi, sorry, but can you please explain what is cc? and also as you say your date is in ccyyddd format so your example is '2013050' so here ddd = 050 so i thinkit is not possible.

Vulpes · Answer

That looks like it should work, Jignesh, except the days will need to be reduced by 1 since 1st January is itself day 1: select DATEADD(d,CAST(SUBSTRING(@value,5,3) as int) - 1 , cast(SUBSTRING(@value,1,4) + '-01-01' as date))

Jignesh Trivedi · Answer

hi, Thanks Vulpes for given understanding.. Atul try, Declare @value varchar(7) = '2013050' select DATEADD(d,CAST(SUBSTRING(@value,5,3) as int),cast(SUBSTRING(@value,1,4) + '-01-01' as date)) here i am assuming first date of year is 1st jan. it might help you.

Mai Hu Na · Answer

hi this is possible using ccyyddd format

Jignesh Trivedi · Answer

hi, it just look like unvalid date because 7 digit date '2013050' if we part this date year is 2013 month is 05 and date is 0? it is not possible of 0 date.. :) you may just refer below link http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx it contain conversion of date in different format hope this will help you.

cast 7 digit string format date into date in sql

Answers (7)