How to make a query to calculate the age

Question

0.00/5 (No votes)

See more:

i want to make a query to calculate the age for the client auto in a column i have the Civil number for him like (286063001795)

Quote:
286063001795
((2 = 19 , 86 = 86 )(1986 year)) , 06 = 6 month , 30 = 30 days , 01795 = don't need them
(1996/6/30)
-
(today)
=
AGE

how can i make something like this

What I have tried:

i try many thing bout what i found is making it manual so i want to make it by query any help

Posted 1-Jan-19 23:51pm

el_tot93

Updated 3-Jan-19 0:26am

v3

Add a Solution

Comments

Maciej Los 2-Jan-19 6:17am

What Sql Server version?
If i understand you well, the hardest part is to "convert" civil no. into a date.

el_tot93 2-Jan-19 6:18am

2017

BillWoodruff 2-Jan-19 6:23am

so, focus first on proof-of-concept: write a method that takes a "civil" and returns a DateTime ... then figure out the SQL stuff.

el_tot93 2-Jan-19 6:25am

thx for your answer bout im new in coding so i don't know how to do that

BillWoodruff 2-Jan-19 7:05am

We were all new in coding, once :) For a newcomer, you are dealing with some advanced challenges here.

3 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 · Accepted Answer · 2019-01-02T00:28:00

Solution 1

First, make a DATETIME value - that's pretty easy:

SELECT 
   Civil,
   CONVERT(DATETIME, 
      CASE WHEN SUBSTRING(Civil, 1, 1) = 2 THEN '19' ELSE '20' END + 
      SUBSTRING(Civil, 2, 2) + 
      '/' + SUBSTRING(Civil, 4, 2) +
      '/' + SUBSTRING(Civil, 6, 2), 
      120) AS DOB,
FROM MyTable

Now comes the difficult bit ... Age is a difficult concept, because it's not a fixed value, it's relative to the current date and it also has a number of components.
Getting it relative to the current date isn't too difficult - GETDATE will give that to you and you can easily subtract one from the other. The problem is the parts: x years, y months, and z days. You would need to calculate the timespan, and then work out the parts separately. See here: sql server - How to calculate age (in years) based on Date of Birth and getDate() - Stack Overflow[^] which gives a solution.

Me? I'd return the DOB from SQL using the code above, and then process the date in C# to give the age: Working with Age: it's not the same as a TimeSpan![^]

Actually, no, I wouldn't: I'd scrap the "Civil" format and store the DOB in a DateTime value instead where it's a lot easier to process. The "Civil" format can be generated from the "real" DOB pretty easily.

Posted 2-Jan-19 0:28am

OriginalGriff

Comments

CHill60 2-Jan-19 6:32am

"The 'Civil' format can be generated from the 'real' DOB" - only if the 01795 has been stored as well. I wholeheartedly agree with the store the DOB not the age though!

OriginalGriff 2-Jan-19 6:41am

I'd probably want to know what that encodes - probably the time of birth? - and store that in a sensible format as well if it is at all relevant.

But working with "semi-encrypted" values in SQL is a PITA at the best of times ...

Maciej Los 2-Jan-19 6:36am

You are pretty fast, Paul!
5ed!

el_tot93 2-Jan-19 6:47am

Argument data type float is invalid for argument 1 of substring function.

OriginalGriff 2-Jan-19 6:54am

You are storing the Civil value in a FLOAT?
You are mad.

That complicates matters a lot: instead of SUBSTRING, you will have to convert your FLOAT value to an INT, then use divide and modulus to extract each digit / digit pair prior to building your DATETIME value.

Seriously, dump it and use DOB as a DATETIME. It's a whole load easier to work with...

el_tot93 2-Jan-19 6:56am

so i will convert the column to int

OriginalGriff 2-Jan-19 7:02am

That solves one part - you still have to play with divide and modulus every time you use it. It's a lot simpler to store DATETIME values and generate you "Civil" number only when you actually need it, honest!

Just look at how many people here are saying "Change the way you store it" Do you think we are all wrong? :laugh:

CHill60 2-Jan-19 7:48am

If you're going to do it that way then you will need to use BigInt not Int

OriginalGriff 2-Jan-19 7:56am

Good point - I forgot you would only get 10 digits at best in an INT.

BillWoodruff 2-Jan-19 7:00am

+5

Maciej Los · Accepted Answer · 2019-01-02T00:35:00

If i understand you well, the hardest part to resolve your issue is to "convert" civil no. into a date.

Well, MS SQL-Server 2017 has got a built-in function which can return date from parts: DATEFROMPARTS (Transact-SQL) - SQL Server | Microsoft Docs[^]
All what you have to do is to pass proper values: year, month and day. Finally, you can get result in years, months, etc. by using: DATEDIFF (Transact-SQL) - SQL Server | Microsoft Docs[^] function.

SQL

--CivilNo to date parts:
SELECT CASE WHEN SUBSTRING(CivilNo, 1, 1) = '2' THEN 19 ELSE 20 END AS YearPart, CONVERT(INT, SUBSTRING(CivilNo, 2, 2)) AS MonthPart, CONVERT(INT, SUBSTRING(CivilNo, 4, 2)) AS DayPart
FROM YourTableName

Now, you know how to "convert" Civil no. into date parts. The rest belongs to you! Good luck!

For further details, please see:
SUBSTRING (Transact-SQL) - SQL Server | Microsoft Docs[^]
CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Docs[^]

[Note]: Seems that OriginalGriff and me, we have the same idea about converting Civil no. into date, but i'm not so fast in typing and expressing myself as He is!

CHill60 · Accepted Answer · 2019-01-02T00:41:00

Solution 3

In addition to OG's solution above, I assumed you would be storing the Civil number as … a number so I came up with something like this...

Get the date of birth in xMMDD format by dividing the civil number by 100000 (where x is 1, 2 as per your rules).

Convert that to a date in CCYYMMDD format by adding 17000000

Convert that number to a real date.

SQL

declare @demo table (id int identity (1,1), civicNo bigint, dob date)
insert into @demo (civicNo) values (286063001795),(260102901794)

update @demo set dob = CONVERT(datetime, convert(varchar(10), 17000000 + (civicNo / 100000)))

As you will see from the link in solution 1 there are lots of ways of calculating age - I tend to use the following method but be aware that I haven't tested it across all combinations of leap year in birth year, leap year in "today", day of date of birth etc.

SQL

select *, datediff(Month, dob, getdate())/12 as age, (civicNo / 100000),17000000 + (civicNo / 100000)
from @demo

I completely agree with @OriginalGriff though - don't store data that is that dynamic on the database, calculate it when required.

Posted 2-Jan-19 0:41am

CHill60

Comments

BillWoodruff 2-Jan-19 7:03am

Ahoy: magic numbers sighted :)

CHill60 2-Jan-19 7:48am

:blush:

Maciej Los 2-Jan-19 7:24am

Caroline, i don't know how you do that, but you're awesome when you play with sql!
5ed!

el_tot93 2-Jan-19 7:49am

bro how can i do it with my database im new in coding and i try muny time
table ( tabl )
civicNo ( CIVILIDD )
dob ( dob )

CHill60 2-Jan-19 7:51am

How can you do what? I don't understand the rest of your comment - what is a tabl, a CIVILDD or a dob

el_tot93 2-Jan-19 7:54am

i have a table with that column name
i try your code but i can't do it

CHill60 2-Jan-19 7:58am

How about

update tabl set dob = CONVERT(datetime, convert(varchar(10), 17000000 + (CIVILIDD / 100000)))

el_tot93 2-Jan-19 8:02am

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

CHill60 2-Jan-19 11:34am

column dob should be of type date or datetime. You are obviously using a varchar. Always use an appropriate column type for the data you intend to store in it!

el_tot93 2-Jan-19 11:36am

ok i make it thx a lot bro thx for your help

el_tot93 3-Jan-19 0:43am

how to convert float to bigint ?????????

CHill60 3-Jan-19 3:45am

Either drop the table and recreate it using a bigint instead of a float for CIVILIDD or you can use CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Docs[^]. E.g. (untested)

CONVERT(bigint, CIVILIDD)

or

CAST(CIVILIDD AS bigint)

The best approach is to change your table schema to use the correct data types for the columns

el_tot93 3-Jan-19 5:42am

sorry bro bout it gave me the same error i need to have something because i have many wrong CIVILD number and gave me error because that

Conversion failed when converting date and/or time from character string.

CHill60 3-Jan-19 6:22am

Show me your create table script and the actual query you are running

el_tot93 3-Jan-19 6:27am

i do it with answer reply

el_tot93 3-Jan-19 7:11am

is there something wrong in my answer

CHill60 3-Jan-19 8:12am

Never post questions or comments as solutions (answers). It will be downvoted and reported into oblivion and the information will be lost. I've managed to grab a copy, but in future use the "Improve Question" link to add the extra information to your question

CHill60 3-Jan-19 8:17am

The code you posted works. No errors are reported.

el_tot93 6-Jan-19 0:05am

sorry bro but i still have the same error msg i think because i have a wrong Civil No ?????? or that not the issue

CHill60 6-Jan-19 6:51am

What data gives you the error?

How to make a query to calculate the age

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us