Get only year part from date string in SQL server

Question

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 30-Nov-21 19:20pm

Member 11052432

Updated 1-Dec-21 2:11am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2021-11-30T19:41:00

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!

Posted 30-Nov-21 19:41pm

OriginalGriff

Comments

Member 11052432 1-Dec-21 3:19am

HiThanks 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.

CHill60 · Answer 2 · 2021-12-01T02:11:00

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