Click here to Skip to main content
15,174,500 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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 3:11am

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!
   
Comments
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.
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
   

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




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