cast 7 digit string format date into date in sql

Mai Hu Na

13y3kSQL

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

please help

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes13y ago
Accepted 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 < 13; i++)
{
if (ddd <= days[i])
{
month = i;
break;
}
}

return new DateTime(year, month, ddd - days[month - 1]);
}
}
Jignesh Trivedi13y ago
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.
Vulpes13y ago
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 Trivedi13y ago
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.
Jignesh Trivedi13y ago
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.
Mai Hu Na13y ago
hi this is possible using ccyyddd format
Jignesh Trivedi13y ago
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.