Hi group!

If I have this 040201 which is 4 yrs 2 months and 1 day, I would like to know how many day from the number above. Is there any function in SQL which I can use to convert to the days? Thanks for all helps!
Ex:
040101 = 1492 days
000101 = 31 days
000001 = 1 days

Thanks!

I don't think the question is logical. In the first example the number of days would depend if there were a leap year or not. 040101 (four years, 2 months and a day. What if it were one year, no months and no days. The number of days would depend if there were a leap year in the particular calander year.

In the second example the answer could be different depending on the number of days in a month.

declare @YourFormat char(6)
declare @RealDate smalldatetime

set @YourFormat = '040201'

set @RealDate = dateadd(dd, cast(substring(@YourFormat, 5, 2) as int), dateadd(mm, cast(substring(@YourFormat, 3, 2) as int), dateadd(yy, cast(substring(@YourFormat, 1, 2) as int), CURRENT_TIMESTAMP)))

print cast(@RealDate as varchar(21)) + ' is today plus ' + @YourFormat

-- If you just want to assume 365 days in a year and 30 days in a month
-- then:

declare @NumDays int
declare @YourFormat char(6)

set @YourFormat = '040201'

set @NumDays = cast(substring(@YourFormat, 1, 2) as int) * 365
                + cast(substring(@YourFormat, 3, 2) as int) * 30
                + cast(substring(@YourFormat, 5, 2) as int)

print @NumDays