Convert Decimal or Varchar into Date

sql sql-server date casting converter

24,185

Solution 1

To convert the number 19650101 to a date use

CONVERT(DATETIME, CAST(Birth AS VARCHAR(8)), 112)

To get the number of years (to one decimal) you could do:

ROUND(
    DATEDIFF(day,
         CONVERT(DATETIME, CAST(Birth AS VARCHAR(8)), 112),
         [Date])/365.25
     ,1)

Which won't be exact but should be close enough to tenths of a year.

Solution 2

You can use this:

-- Create demo data
CREATE TABLE #dates(birth int, date date)

INSERT INTO #dates(birth,date)
VALUES(19650101,N'2015-07-09')

-- Your work
SELECT CONVERT(date,CONVERT(nvarchar(max),birth),112) as birth, date, 
        DATEDIFF(year,
            CONVERT(date,CONVERT(nvarchar(max),birth),112),
            date
        ) as years
FROM #dates

-- Cleanup
DROP TABLE #dates

This depends on the exact format you provides (19650101).

24,185

Author by

Nils

Updated on July 10, 2020

Comments

Nils almost 4 years
I have 2 fields.

Birth= Datatype Decimal(12,4) & Date = Datatype Date
```
Birth         Date 
19650101   2015-07-09  
```
how do i get a result that looks like this

i want the result to be like this
```
Birth             Date          
1965-01-01       2015-07-09       
```
where the Birth is a Date datatype and not a decimal (12,4)
Nils almost 9 years

i have change my question.
Sean Lange almost 9 years

Be careful here. DATEDIFF counts the date thresholds that are crossed so your years are not going to be accurate. For example if you have a start date of 2014-12-31 and an end date of 2015-01-01 datediff will return 1 and it has only been a single day. Not saying this approach is wrong, just making sure the OP understands the nuances of datediff.
Ionic almost 9 years

Your question is the same? By the way, Your format can still be used. Birth is in the format you desires. I know that this may be inaccurate on edge cases. Instead you can use days and calculate the days under recognition of shift-years.
Nils almost 9 years

when i try to convert the Birth that has an datatype of decimal to varchar i get this error. Arithmetic overflow error converting numeric to data type varchar.
Sean Lange almost 9 years

Thats because you have to make the varchar 13 because your decimal is 12, 4
Nils almost 9 years

i get this error from your answer to. CONVERT(date,CONVERT(nvarchar(max),birth),112) = Conversion failed when converting date and/or time from character string.
Nils almost 9 years

still the same error the datatype of the Birth field is Decimal(12,0) = Conversion failed when converting date and/or time from character string.
D Stanley almost 9 years

@Nils Then you have one or more values that does not represent a valid date.
Nils almost 9 years

yes my bad. i hade 12 characters in the field.. now it workes
Gordon Linoff almost 9 years

I would never use nvarchar() max for this conversion. To hold 8 digits, you just need varchar(8). But the idea is correct.
Ionic almost 9 years

Well in the conversion it won't count in any way. I normally take bigger nvarchar values for the conversion instead of risking conversion errors due to too small values.
Tk1993 about 3 years

for 44119.5253587963 its failing: Conversion failed when converting date and/or time from character string.
D Stanley about 3 years

@Tk1993 The answer only works for numbers that are equivalent to a date in the format YYYYMMDD - yours is not, so I wouldn't expect it to work.