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(dCAST(SUBSTRING(@value53) as int) - 1 cast(SUBSTRING(@value14) + '-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(dCAST(SUBSTRING(@value53) as int) - 1  cast(SUBSTRING(@value14) + '-01-01' as date))

Jignesh Trivedi · Answer

hi Thanks Vulpes for given understanding.. Atul try Declare @value varchar(7) = '2013050' select DATEADD(dCAST(SUBSTRING(@value53) as int)cast(SUBSTRING(@value14) + '-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.