15,311,561 members
See more:
```I have following Date string format :-

2.04 Years
0.06
2 Years
0
Now my requirement is ->

extract Year as 2 / 0 without Years keyword.
extract Month as 4 / 6 / Calculate month from above like calculate month from 2 years```

What I have tried:

Quote:
I tried with split / substring / PARSENAME function but some different results returned.
Posted
Updated 1-Dec-21 2:11am

## Solution 2

Here is a sql method partially done for you
SQL
```declare @demo table ([data] varchar(50));
insert into @demo ([data]) values
('2.04 Years'),
('0.06'),
('2 Years'),
('0'),
('0.5 years');

-- Get the years as a float value
;with cte as
(
select
-- may need to use COLLATE to handle case sensitivity
cast(REPLACE([data], ' years','') as float) as Transformed
from @demo
)
select
-- get the whole number part as "years"
cast(Transformed as int) as [Years]
,'substract the years from the float as input to months calculation' as [Months]
from cte;```
Note I have not done the months calculation for you but have included a hint as how to do it.

If nothing else this demonstrates OriginalGriff's points - handling dates in this way is ugly, prone to issues (see my comment about case sensitivity) and horribly non-performant

This appears to be elapsed time that you are trying to store rather than a "Date", so you might want to consider using a float instead - the sql is much simpler
SQL
```declare @demo2 table ([data] float);
insert into @demo2 ([data]) values
(2.04),
(0.06),
(2),
(0),
(0.5);
select
cast([data] as int) as [Years]
,'substract the years from [data] as input to months calculation' as [Months]
from @demo2```
Again - I have left the calculation of the months as an exercise for you.

By far the best approach would be to store the start datetime and the end datetime and calculate the difference when you need it - preferably in the UI layer

## Solution 1

Basically, don't do it with SQL: do it in your presentation language, and pass a DATE, DATETIME, or DATETIME2 value as a parameter to SQL for storage.

SQL string handling isn't good, or particularly flexible and it's much more sensible to divorce the data format you receive from wherever from the data storage. That way, if the format changes slightly it's much easier to detect and deal with.

It can be done in SQL, but it's truly a PITA and much, much easier in almost any presentation language!
Member 11052432 1-Dec-21 3:19am

Hi Thanks for your suggestions.

But I want to do it from SQL ONLY.

Kindly provide a solution if you have.
OriginalGriff 1-Dec-21 3:54am

That isn't the way it works: While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 230 Richard Deeming 165 Greg Utas 60 Dave Kreskowiak 60 Patrice T 60
 OriginalGriff 4,269 Richard MacCutchan 1,608 CPallini 1,110 Richard Deeming 948 Patrice T 775

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900