find age using query in textbox

Bhavesh Vankar

5y1.1kVisual Basic .NET

how can i find age of user when enter date like "23/03/1982" then age should be display in textbox using sql query.

actualy i want to find age using server date. by sql query....

how to implement using sql query...

below is my query how can i implement this query to vb6.0 project...

select DATEDIFF(YEAR,CONVERT(datetime,'23/03/1993',103),CONVERT(datetime,GETDATE(),103))

Textbox1 is for date. date should be dd/MM/yyy

Textbox2 is for age.

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtesh Shah5y ago
try this
rs.Open "Select DATEDIFF(hour,CONVERT(datetime,'" & txtBirthdate.Text & "',103),CONVERT(datetime,_ISSUEDATE,103))/8766 as V_AGE from VISITOR_MASTER_NEW", cn.Conn, adOpenDynamic, adLockOptimistic
txtAge.Text = rs("V_AGE") & ""
Bhavesh Vankar5y ago
i want to display date like below article. but their is used button and datetimepicker but i have used textbox instead datetimepicker.

i want to use only two textbox. 1 for enter date and second for display date using vb6.0

https://www.webcodeexpert.com/2013/07/get-age-in-yearsmonthsdayshours-and.html
Bhavesh Vankar5y ago
ya its work fine but my question is how to implement this query in my project when user enter birthdate then age should be display in textbox.
i can't understand how to implement this...?
can you share whole code to do this....i am new in vb.
my old project have some update
Kirtesh Shah5y ago
Hi Bhavesh,
Try this,
rs.Open "Select DATEDIFF(YEAR,CONVERT(datetime,'" & txtBirthdate.Text & "',103),CONVERT(datetime,V_ISSUEDATE,103)) as V_AGE from VISITOR_MASTER_NEW", cn.Conn, adOpenDynamic, adLockOptimistic
txtAge.Text = rs("V_AGE") & ""
I suggest to use this quesry instead of your query.
select DATEDIFF(hour,CONVERT(datetime,'22/12/1980',103),CONVERT(datetime,GETDATE(),103))/8766 as age
Try your query with '22/12/1980' date. It will give you result 41. Ideally it should be 40.
Bhavesh Vankar5y ago
i need age in round age like 10, 50, 45, etc...
i have make it like as below.....
and also set textbox event is lostfocus...
i also need if date is not in format dd/MM/yyyy then it should be show error please enter date in DD/MM/YYYY format.
kindly see my code and suggest.
If txtBirthdate.Text = Format(txtBirthdate.Text, " dd/MM/yyyy") <> True Then
MsgBox "Please Enter Date In dd/MM/YYYY Format", vbInformation
End If
    If rs.state = adStateOpen Then rs.Close
rs.Open "Select DATEDIFF(YEAR,CONVERT(datetime,'" & txtBirthdate.Text & "',103),CONVERT(datetime,V_ISSUEDATE,103)) from VISITOR_MASTER_NEW", cn.Conn, adOpenDynamic, adLockOptimistic
txtAge.Text = ("V_AGE") & ""
Kirtesh Shah5y ago
Hi Bhavesh,
Do you need Age in decimal or round age? Based on the requirement use query in code.
SELECT DATEDIFF(hour,CONVERT(datetime,'23/03/1993',103),GETDATE())/8766.0 AS AgeYearsDecimal, DATEDIFF(hour,CONVERT(datetime,'23/03/1993',103),GETDATE())/8766 AS AgeYearsIntTrunc
Secondly, after executing
Set objRs = objCmd.Execute

you need to read Recordset(objRs ) . Fetch the output and set to Textbox2.
Hope it will help you.
Bhavesh Vankar5y ago
hello kritesh sir...
Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objRs As New ADODB.Recordset

    objCmd.CommandText = "SELECT DATEDIFF(hour,CONVERT(datetime,'"& Textbox1.text &"',103),GETDATE())/8766.0 AS AgeYearsDecimal
, DATEDIFF(hour,CONVERT(datetime,'" & Textbox1.text &"',103),GETDATE())/8766 AS AgeYearsIntTrunc "

    objCmd.CommandType = adCmdText

    ' Connect to the data source.
    Set objConn = GetNewConnection
    objCmd.ActiveConnection = objConn

    ' Execute once and display...
    Set objRs = objCmd.Execute
i am little bit confused when i put this code in Textbox 1 event is change of textbox.. the code not give any result or error.
and one confussion is how to display output in txtage.text. textbox.
Siddharth Gajbhiye5y ago
Hello Bhavesh

try using the below code
DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '2/29/04'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days
Kirtesh Shah5y ago
You can use the below query to get age in decimal or round age as per your requirement.
SELECT DATEDIFF(hour,CONVERT(datetime,'23/03/1993',103),GETDATE())/8766.0 AS AgeYearsDecimal
, DATEDIFF(hour,CONVERT(datetime,'23/03/1993',103),GETDATE())/8766 AS AgeYearsIntTrunc
You can get result as per below,
Use this query in VB 6 like below,
Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objRs As New ADODB.Recordset

    objCmd.CommandText = "SELECT DATEDIFF(hour,CONVERT(datetime,'"& Textbox1.text &"',103),GETDATE())/8766.0 AS AgeYearsDecimal
, DATEDIFF(hour,CONVERT(datetime,'" & Textbox1.text &"',103),GETDATE())/8766 AS AgeYearsIntTrunc "

    objCmd.CommandType = adCmdText

    ' Connect to the data source.
    Set objConn = GetNewConnection
    objCmd.ActiveConnection = objConn

    ' Execute once and display...
    Set objRs = objCmd.Execute

I hope, it will help you.
Sachin Singh5y ago
whatever you have used is correct but may give wrong results sometimes, better is to create a user defined function and use it in your query
CREATE FUNCTION AGE(@DateOfBirth AS DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @Years AS INT
DECLARE @BirthdayDate AS DATETIME
DECLARE @Age AS INT
--Calculate difference in years
SET @Years = DATEDIFF(YY,@DateOfBirth,GETDATE())
--Add years to DateOfBirth
SET @BirthdayDate = DATEADD(YY,@Years,@DateOfBirth)
--Subtract a year if birthday is after today
SET @Age = @Years -
CASE
WHEN @BirthdayDate>GETDATE() THEN 1
ELSE 0
END
--Return the result
RETURN @Age
END
now you can use it in query as
select dbo.AGE(DOB) as age from table;