How To Get Date Of Birth And Age In Years?

Mar 27, 2007

I have a table name employee and datetime column named dateofbirth, how can i write a select statement to show their, date of birth and age in years?

This is how i did it, i couldn't finish it, Any feedback would be very thankful.

select name, datebirth, year(dateofbirth)
from employee

View 10 Replies


How To Get Age From Date Of Birth

May 15, 2002

Hello Everyone,

I have a table containing a column "date of Birth" in char datatype, I need to calculate Age for the person from his or her date of birth. how would I be able to do that?

Any help will be appreciated.


View 3 Replies View Related

Selecting Date Of Birth

Jun 14, 2008

I have table with column Date Of Birth its datatype is smalldatetime. Now I was looking for SQL Statement like I will give from date and to date as parameter it should select date of birth occurring between that date and month.

View 5 Replies View Related

Date Of Birth Conversion

Apr 18, 2000

Can someone help please?
I'm using SQL Server 7 and trying to update a table containing over 250,000 records. I have a character(8) (CHARDOB) field representing dates of birth in the form YYYYMMDD and would like to update my datetime field (DOB) using a simple update command like:-

5, 2) + '/' + LEFT(CHARDOB, 4)

but get the message:-

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

The strange thing is that this works on a small dataset but crashes and burns on larger ones. I have tried different CAST and CONVERT options to transform the data into the correct format. I have tried a stored procedure, DTS transformations and execute SQL task in my DTS package without success.

Any help will be appreciated.

View 4 Replies View Related

Birth Date 1900-01-01 00:00:00.000

Apr 12, 2005


How to check the default value set up for datetime field? The reason I am asking is my database

field "birthday" is datetime(8), the default value I set to '' already. Everytime my asp program
have empty birthday or invalid birthdate for this field, the system automaticaly set it to

1900-01-01 00:00:00.000

Do you know what happened?


View 3 Replies View Related

Average Date Of Birth

Jul 8, 2004

Hi, folks.
I have a table with a column Date_of_birth.
I want to evaluate average date_of_birth by grouping on depts.
AVG function doesn't seem to work with datetime col. Plz help.

View 8 Replies View Related

Get Date Of Birth Query Problem...

Jun 7, 2007

i want to make one SQL Query in that i have problem so please help me
i have three column  ID,Name,DOB
in that i want top 5 name whose Birthday is incomming   days....
reply as soon as..

View 7 Replies View Related

Trigger To Calculate Age From Date Of Birth

Dec 12, 2003

Here's the problem. I want to insert age in years to a table of children, using a MS SQL trigger to calculate age from Date of Birth (DOB). DOB format is mm/dd/yyyy. The DOB input comes from an ASP Insert statement. I've tried to use DateDiff and a user-defined function to calculate age without any success. Also the trigger needs to account for children of less than 1 year old, could be a 0. Age is integer in the children table. Any help is greatly appreciated. The sooner the better. Even a kick start is better than nothing.

View 11 Replies View Related

Converting Birth Date Format

May 6, 2014

I am trying to convert the birth dated from 19591229 to 12/29/1959 and using below code but its not giving me in that format its printing line 1959-12-29. how do I do this?

bdate=convert(date, p.date_of_birth,101)

View 10 Replies View Related

Formatting For A Birth Date Field??

Sep 16, 2006

Hello, I'm new to SQL Server, working for a non-profit computerizing alot of its data.I imported a table of people's names, birth dates, etc. into SS2005from Access, and the birth_date was imported as an Access date/timefield, giving it the datetime datatype in SQL.The column values look like:10/14/1964 12:00:00 AMWhere and how do I learn to specify that all fields like this should bein ISO format of yyyy-mm-dd??Do I have to create a new column and put all the dates into it??Should I just convert the data in queries/views??Use a constraint to format the data??I can redo the Access table if necessary, it is only 300-some rows.I tried BOL but it was not helpful...The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will haveto be stored properly in the database table as column/fieldbirth_date...Thank you, Tom

View 11 Replies View Related

T-SQL (SS2K8) :: Formatting Date Of Birth Using RSA ID Number

Nov 8, 2012

I need creating date of birth using ID number the ouput that im looking is a follows

e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01
e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01

The desired format I need is to take the above and create date of birth with the below format as required by the application used.

01 Jan 1980
01 Jan 2000

View 9 Replies View Related

Creating Birth Date From Day, Month, Year Fields

Nov 14, 2007

I have 3 fields (DOB_YEAR, DOB_MO, DOB_DAY)

They are populated like this (1985, 12, 21)

I would like to take the 3 values of populate one field (BIRTH_DATE) with these combined values to look like this (19851221) BIRTH_DATE is an 8 character field in datetime format.

The code

SET BIRTH_DATE = (dob_year+dob_mo+dob_day)

Returns 6/11/1905 for 1959,11,17

View 5 Replies View Related

Get Date Of Tuesday Of Past X Years From Now

Feb 4, 2015

I am trying to get the date of Tuesday from now of the past x Years

The below Query works fine for weeks

SET x= 53

The issue is with below query for any passed Number of Years

SET x = 7

So need to get past years Tuesday Date and also verify if week also works for any passed week numbers ? The x parameter can be changed based on the situation

View 9 Replies View Related

SQL Server 2012 :: Sales Over Years - Retrieve Values In Single Query For Multiple Years And Grand Total?

Apr 24, 2015

I need to list customers in a table that represents sales over the years.

I have tables:

Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...

I am using this SQL but it only gets one year:

SELECT , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON = Products.idOrder
INNER JOIN Customers ON = Orders.idCustomer
WHERE year(date)=2014

I need something like this:

customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$

Is it possible to retrieve these values in a single SQL query for multiple years and grand total?

View 6 Replies View Related

SELECT DISTINCT To Return Only The YEARS In A Date Field?

Mar 22, 2006

I have a table in my MS SQL 2000 database called News which has a field caled NewsDate. This is a standard Date field which stores the info in this format: 3/1/2001.

I want to create a query that returns one row for each year that there is a story.

For example, if I had this data...
3/1/2001, 6/27/2003. 9/17/2003, 1/1/2006, 4/5/2006

the query would return this result:


This is the query I've started with:


What modifier can I apply to the NewsDate field to extract JUST the year from the table? If this were ASP I would try something like Year(Date), but, of course, I can't do that here.

Is this even possible? I've been looking up date functions, but haven't found anything that will work in a select statement. ANY and ALL advice will be greatly appreciated.

View 1 Replies View Related

SQL Server 2012 :: Get Previous Years Holiday Date?

Jan 6, 2015

Given this table;

DECLARE @table TABLE (HolidayDate DATE, HolidayName NVARCHAR(50))
( HolidayDate, HolidayName )
VALUES ('2012-01-01','New Years Day'),
('2012-01-16', 'MLK Day'),
('2012-02-20', 'Presidents Day'),


How to get a result set that shows a new column called PreviousHolidayDate with the corresponding holidays last years date?

1/1/2012New Years DayNULL
1/16/2012MLK DayNULL
2/20/2012Presidents DayNULL
4/6/2012Good FridayNULL
5/28/2012Memorial DayNULL


View 4 Replies View Related

T-SQL (SS2K8) :: Calculate 90 Days And 3 Years Ago From Effective Date In A Table?

Sep 30, 2014

What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?

as in

SELECT EffectiveDate
from FL.CEFHistory

I need to return the effective date - 90 days and 1 year from that.


View 6 Replies View Related

How Many Years

Sep 28, 2005

Of database experience do you have?

And what are they?

View 14 Replies View Related

Happy New Years

Dec 30, 2004

I just wanted to wish everyone a Happy New Years and I wanted to thank all submitters to this forum for breaking up my code monkey workday and cutting into my productivity and I have found many of these discussions helpful in my preperation for my long procrastinated MCDBA exams.

Get hammered and do not drive.

View 2 Replies View Related

Calculating Age In Years

Dec 19, 2001

--Use the following formula to calculate a person's age in years in a stored procedure,
--where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:

DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = '12/06/1966'
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)

--Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now.
--The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.
--The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement
--the "year" part of the function to calculate the number of year boundaries crossed.

View 20 Replies View Related

Add 28 Years Aagin

Jul 20, 2005

HI all,This has puzzled me all morning. I have a int field which reads19691124 (UK dates) this is actually a date 24 November 1969 I need toadd 28 years to this making it 19971124 but Iím stumped!Any ideas anyone?CheersSean*** Sent via Developersdex ***Don't just participate in USENET...get rewarded for it!

View 4 Replies View Related

Happy New Years Dbforums

Dec 30, 2005

remember if you drink too much, get a taxi and charge it to your company.

View 4 Replies View Related

Measuring Consecutive Years

Oct 24, 2006

Hi there.

I work for a charitable organization, am new to this form (and sql programming) and trying to create a flag for unique records indicating the number of consecutive years a donor has given.

I have create a sample db idenifying donor, giving year and total pledges with multiple donor records existing for multiple years having donated.

CREATE TABLE mygifts06 (Donor_id varchar (10), Gift_yr nvarchar (4), Tot_pledges numeric (16,2))

INSERT INTO mygifts06 (Id,Gift_yr,Pledges)
SELECT 155758,2005,15.00 UNION ALL
SELECT 155759,2004,25.00 UNION ALL
SELECT 155758,2004,40.00 UNION ALL
SELECT 155757,2005,100.00 UNION ALL
SELECT 155758,2002,30.00 UNION ALL
SELECT 155758,2001,120.00 UNION ALL
SELECT 155755,2003,15.00 UNION ALL
SELECT 155758,2006,80.00 UNION ALL
SELECT 155757,2003,65.00 UNION ALL
SELECT 155759,2005,400.00

For the above dataset, I am trying to create the following output

Donor_id 2_consec_gifts 3_consec_gifts 4 consec_gifts
--------- -------------- -------------- --------------

Do I need to use a cursor for this task? I lack experienced in using cursors is there an alternative method someone could suggest?

Thanks in advance.

View 9 Replies View Related

Need To Get Just Last 2 Years Worth Of Data

Jun 23, 2008


This gives me last years - but they want last years 2007 and 2008

Any suggestions

View 3 Replies View Related

How To Get 20 Years From Current Year

Sep 30, 2014

I want an sql query that wil give me listing of 20 years from current year.

View 2 Replies View Related

Compare The Same Period Of Different Years

May 21, 2008

Hello to everybody, I'm quite new to the Analysis Services world and would like to create a BI Solution for one of my customers with the following problem: I have sales data from the ERP from different years (2005 and on). My customer wants a report that shows the sales of the current year up to the month he wants to specify (e.g. january - march 2008) compared with the sales of the same period of the previous year (e.g. january - march 2007), divided into product category.

Is there a way to build a cube and then a matrix report in Reporting Services that lets the user specify the period for the current year and shows the same period of the previous year. Do I achieve this behaviour with a calculated member in the cube (e.g. ParallelPeriod)

Any hint (or web like example) would be very appreciated.

Best regards


View 1 Replies View Related

Group By Concurrent Years

Nov 8, 2007

Hi and thank you in advance to whomever takes the time to read this entry. I will be as detailed as possible, so I apologize for the length. The information here relates to the automotive industry, but I don't believe that is a very important detail

The overall purpose of this query is to create a compressed set of the data that exists in the database. The de-normalized information is structured like the following (including made-up data):

Code Block
PartNumber Make Model Year
835100 ACURA INTEGRA 2004
835100 ACURA INTEGRA 2003
835100 ACURA INTEGRA 2001
835100 ACURA INTEGRA 2000
835100 FORD FOCUS 2002

There is any number of part numbers, makes, etc in this mix. The query that I currently use is utilizing MAX and MIN functions to determine year range, but as in the sample above, this does not take into account the fact that there could be a year skipped in the middle. I could probably use a cursor to do this, but would really like to stick to set logic if at all possible.

My existing query is:

Code Block
,RTRIM(lv.Make) AS Make
,MIN(lv.Year) AS StartYear
,MAX(lv.Year) AS EndYear
CWIParts AS cwi
INNER JOIN PartTypes AS pt
ON cwi.PartTypeID = pt.PartTypeID
INNER JOIN PartDetail AS pd
ON cwi.PartNumber = pd.PartNumber
ON pd.StatusCode = s.StatusCode
INNER JOIN LegacyVehicle AS lv
ON cwi.LegacyVehicleID = lv.LegacyVehicleID
cwi.PartTypeID = 10
AND s.Status = 'Active'
cwi.PartNumber ASC
,cwi.Make ASC

In hopes to end up with a result set that has 1 row for each unique part-number, make, and consecutive year range relevant to the part-number and make. I will also need to add the functionality to add other attributes (such as Model and Liters), but those are equality based and can be passed in easily.

I had done most of this programatically in, but I would much rather push this logic back to SQL Server. Thank you all for any help that you can provide on this topic.

- Jay Soares

View 9 Replies View Related

Dates In Years Prior To 1900?

Jan 13, 2000

I've set up a SQL7 database with MSAccess97 as a front end. I'm trying to enter a person with a birthdate prior to 1900, get an ODBC call error, "Datetime field overflow". How to enter dates prior to year 1900?

View 1 Replies View Related

Dynamic Sql To Loop Over Fiscal Years

Jan 6, 2004

thanks for reading.

i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?

the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY

problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.

again, thanks for reading ... and any help in advance.

SELECTcount(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data

WHEREstart_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'


SELECTcount(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data

WHEREstart_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'



expected output....

Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
.... ....

View 4 Replies View Related

T-SQL (SS2K8) :: DATEADD And Leap Years

May 29, 2012

DECLARE @MyDate datetime
SET @MyDate = '2/28/2009'

SELECT DATEADD(year,-1,@MyDate)

Desired result '2/29/2008'

This below seems to work. (Subtract a year and then find the last day of the month for that date, set to midnight)

SELECT DATEADD(dd, DATEDIFF(dd,0,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(year,-1,@MyDate))+1,0))), 0)

Is this a reasonable approach? I thought the SQL Server data and time functions were aware of leap year?

View 9 Replies View Related

Subquery In DTS No Longer Works After Years

Oct 13, 2006

Hi folks,A DTS package we have run for years now no longer works. The specificpart that is not working is a subquery in the SOURCE object of atransformation. The source is based on a Microsoft Data Link to aSybase database (DSN changed a couple months ago but the connectionstring was updated successfully for the new 12.51 version of ASE) andthe destination is a link to a local SQL Server 2000 database.The transformation has always worked and when I remove the subqueryeverything works OK. The problem is that I need the subquery!Does anyone have a clue what is going on?Here is the full TableKey = RVSN_TYPE_ID,TableCode = RVSN_TYPE,RevisionDate = RVSN_DATE,RevisionReasonCode = RSN_CODE,RevisionGroup = RVSN_GRP_ID,RevisedField = (select L.FieldIDfrom tempdb.guest.lkpRevisedField Lwhere L.TableID = R.RVSN_TYPEand L.FieldName = R.CHNG_FLD),RevisedValue = OLD_FLD_VAL,RevisionTimestamp = RVSN_TIMESTAMPfrom RVSN R,tempdb.guest.MaxTimeStamp TSwhere R.RVSN_TIMESTAMP TS.Rtimestampand R.RVSN_TIMESTAMP is NOT NULLJohn H.

View 1 Replies View Related

Getting Density Of Values Across Years And Months

Aug 29, 2007

Hi All,

I have the following table "Project"

ID Name Start Date End Date
001 Project 1 2-2-2003 2-3-2007
002 Project 2 1-24-2003 2-6-2007
003 Project 3 4-10-2005 2-10-2008
004 Project 4 5-20-2006 6-6-2008
015 Project 15 2-20-2006 3-3-2009
What I want is the the following output.

Output 1:

Year Projects No. Of Projects Starting Months
2003 Project 1 , Project 2 2 February, January
2005 Project 3 1 April
2006 Project 4, Project 15 2 May , Feb

(the order displayed in the months shoudl be in accordance with the order of the projecs in the projects column...)
and also the following

Output 2: (this is optional view...)
Year Start Date Project Name

February 2 Project 1
January 24 Project 2
April 4 Project 3
May 5 Project 4
February 20 Project 15

I am very much in need of Output 1. Could someone help me,

View 5 Replies View Related

Analysis :: MDX Multiple Years On Columns

Nov 5, 2015

Using 'TAIL([Time].[Time].[Year].MEMBERS,1)' I can get the current year dynamically.  My question is how do I get the previous two years dynamically as well.  I've tried a few different ways with no luck.  As you can see below I'd like to replace 2014 and 2013 with current year -1 and current year -2.

{[Branches].[Branches].[Region]*[Measures].[Ship Resale S&D Run Rate]} ON ROWS

View 9 Replies View Related

Copyrights 2005-15, All rights reserved