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


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

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

3 Month To Date Average

Apr 1, 2008

Hello Friends

Actually i posted this problem earlier , hoping that i got the result , did not look in depth untill i realized the values i got was getting was wrong.

The Scenario is still the same.

3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.

I am creating a Calculated Member as measure.

Basically if i select the average on 76th day of the year, it should basically be the average of 76th Day ( well offcourse whould ignore the empty cells )

For Example the result set should be very similar , It is basically doing MTD

Running balance is basically adding up , where as 3 month to date average should be average of 3 month previous average

suppose today is 75th day , so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)

I have used the lag function

AVG(Hirearchy.Currentmember.lag(2):Hirearchy.Curre ntmember, Measure.abc)

where hirerachy is Year->Month-date

The problem i have here is which i am coming close to conclusion

When i use month level it gives me average of month level , the result on month level is fine. But my requirment is to have it on date level. but how do i have rolling average of 3 month in a date level, if i do a date level with 90 days lag which is not correct which is average of 90 days from current day.

AVG([Tbl Date Key].[Report Hirerachy].CurrentMember.Lag(2):[Tbl Date Key].[Report Hirerachy].CurrentMember,[Measures].[Ab1_Avg]


When i drill down to date level , which would be assumed

AVG([Tbl Date Key].[Report Hirerachy].date.Lag(2):[Tbl Date Key].[Report Hirerachy].date,[Measures].[Ab1_Avg]


its doing a lag on 3 days lag as appose to , I need the lag on 3 months on a day which would be 90 days

should i be doing a lag on days.

The problem at 90 days lag would be every it would lag 90 days average , but what i am looking for it is when it is on the middle of the month it should be

suppose today is 15th day of month, so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)

i am confused , please help

View 1 Replies View Related

Average Date (Multiple Columns)

Aug 22, 2007


SQL Server 2000
I have four columns of date in a table say Date1, Date2, Date3, Date4 in table Tab_A.
I want to get average date of four columns for each row.
Please advise how?

J a w a d

View 2 Replies View Related

Analysis :: Calculation Of average Using DAX AVERAGE And AVERAGEX

Jun 21, 2015

Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:

Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)

The results were, respectively: 701,68; 2120,60...; -669,441; and  finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:

test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...

So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.

I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.

View 2 Replies View Related

Need An Average By Year Of An Average By Month

Feb 15, 2008

I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.

Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '

SELECT name, country_cd from weather_station where platformId=&&platformId;

SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');

with a result of:

-------------------- --
OFFUTT AFB___________US

-- ---------- ---------- ---------- -----------

View 4 Replies View Related


Sep 26, 2007

Hello,I have two tables:[Posts] > PostId, ...[Ratings] > RatingId, PostId, RatingI want to select all posts and add a new column named AverageRating.This new column is the average of all ratings associated to that Post.If a post was not rated then its AverageRating would be NULL.How can I do this?Thanks,Miguel

View 2 Replies View Related

Average Help

Apr 18, 2006

if I have a bunch of times, and want an average of them, how do I do that?


For more details, see this thread:


View 8 Replies View Related


Jun 19, 2007

I am getting the number of transactions for two different months based on dates entered from the user. I need to display the number of transactions along with the average sale. I have figured out how to get the dates and sum the transactions but I can't seem to add in the average....

(@prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime)


select sum(case when orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end ) as PrevMonthCount,

sum(case when orders.orderdate between @thisMonthStart and @thisMonthEnd then 1 else 0 end ) as ThisMonthCount

(how do I add in the average for both months? The column is orders.ordertotal)

View 3 Replies View Related

Getting Average?

Jan 6, 2008

Using MS SQL 2005 server how can I get the average cost price of item 'shirt' from this table please?

code item description cost retail
---- ---- ----------- ---- ------
191 shirt shirt - white 2.30 9.99
170 pants pants - beige 6.34 15.00
196 shirt shirt - red 2.64 9.99
199 shirt shirt - blue 2.61 9.99

View 2 Replies View Related

How To Get Average Of Average.

Jan 19, 2008

Hi !

I am having this problem. I have a grouping someting like this.

Resolved Resolution Time Average Resolution Time
30 2904.46 96.82

54 1442.03 26.70

0 0.00 0.00
0 0.00 0.00

Avg : 30.88

Now to calculate the average inside the grouping I have used this

iif(Sum(Fields!ResolutionTime.Value) = 0 ,"0.00",(Sum(Fields!ResolutionTime.Value)/(iif(Count(Fields!ResolvedDate.Value)=0,1,Count(Fields!ResolvedDate.Value)))))

I am at a loss how to calculate the group footer (Result = 30.88 ) which is equal to (96.82 + 26.70 + 0 + 0 ) / 4 . This is the result from the existing Crystal report. I am trying to convert this report to reporting services. Now I cannot nest aggregate functions and also the row number can be dynamic I am confused how to resolve it.

Any ideas will be greatly appreciated.

Thanks in advance.


View 9 Replies View Related


May 21, 2008

Hi All,

I was hoping someone could help me with pluging moving average into my report.

This is the template I am using for moving average:
/*Returns the average value of a member over a specified time interval.*/
AS Avg
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) :
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember,
[Measures].[<<Target Measure>>]
// This calculation returns the average value of a member over the specified time interval.
FORMAT_STRING = "Standard";

This is what I have before I started to add the moving average. The report works but only displays the cummulative complexity rank.


MEMBER [Measures].[Date Key] AS




[Measures].[Date Key],




[Work Item].[System_State].[System_State],




FROM [Team System]



STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),





What I want to do is also add the moving average for this over the parameter of Iterations. (IterationParam). This is what I have so far with the moving average in my report: I know I must have a lot of errors but I can't get past this first error. I highlighted the line with error. The error said: Query(7, 15) Parser: The syntax for '.' is incorrect. (msmgdsrv)



[Measures].[Date Key] AS


[Measures].[Moving Average] as





{measures.[moving average],measures.[Internet Total Product Cost]} on 0,

[Date].[Date]. Members

[Measures].[Date Key],




[Work Item].[System_State].[System_State],




FROM [Team System]



STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),




Does anyone have any suggestions?


View 11 Replies View Related

Getting An Average From A Table

Jan 5, 2007

I have two tables, table a holds all the votes by users of each element in table b. I was wondering, how do I get the average of all those votes in table a that relate to that each instance of a element in table b. For example table b has two elements created by a certain user that has been voted 5 times each by users with scores like 2, 5, 4, 2 , 2 for both of them. I just need to get the average of those numbers that pertain to those elements in table b. Thanks for any help.  

View 4 Replies View Related

Moving Average

Oct 25, 2000

Can any one be able to help me to write a querry on Moving Average


Product Volume

View 2 Replies View Related

Average Of Middle 90%

Sep 21, 2007

Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?

View 9 Replies View Related

Max Average Salary

May 8, 2012

Select * from personnel

where salary > ALL (SELECT AVG (salary) from personnel group by bolno)

And how i find max average salary?

View 8 Replies View Related

Finding Average

Apr 9, 2008

Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".

I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.

Shifts | Mon_day | Mon_Night | Tue_Day.....
Average | 5.2 | 10.2 | 15.2........

The codes that i tried..

declare @Weekday tinyint, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())

if (@Weekday= 1 and (@hour>= 7 AND @hour<19))
Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average'
FROM Weekly

if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7))
Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average'
FROM Weekly

View 7 Replies View Related

Average With Condition

May 28, 2008


fldyear fldascending flddecending
2007 20 23
2006 21 14
2007 41 12
2007 12 5

how do i find the average of fld ascending and descending where year=2007

Your help will be appreciated

View 6 Replies View Related

How To Get Average Number

Jul 12, 2013

How do you get the average number in sql?

View 11 Replies View Related

Moving Average

May 22, 2006

Can anyone tell me how to calculate a moving average for the below table?
MONS /vol /counter(the date)
jan/8 / 2006-01-01
feb/9 / etc.
oct/5 / etc.
nov/6 /
dec/7 /
--------------------- I am using the below code, but it is having no effect......
DATEDIFF( month , i2.COUNTER , i1.COUNTER ) Between 0 And 12
) AS MovingAverageOver12Months

thanks in advance, dirwin

View 2 Replies View Related

How Can I Get The Average Datediff?

Feb 22, 2007

I made a code to in SQL reporting services for the reports of our company regarding the new applicants. Now I need to get the average datedifference in each phase from all applicants who entered Initial/Data Collection. Example, "Initial/Data collection"PHASE will have an average of 60 minutes to be finished, averaged from all 200 applicants, and "Screening"PHASE 240 minutes to be finished, averaged from 100 applicants.

All applicants must go through stage 1 before he can go to stage 2 and so on.

The date diff will be obtained from the "createdon" column subtracted to the last "modifiedon" column in each phase.

I need to insert that code to the existing code that I have if it is possible(which my boss prefer); or either if i can manipulate it in SQL reporting services in visual studio 2005.

Here is a sample output that I have.Each phase has a different color.

Here is the code that i used
SELECT subject, regardingobjectidname, createdon, modifiedon, stat, phase, initial,
(CASEphaseWHEN 'Initial/Data Collection' THEN 1
WHEN 'Screening' THEN 2
WHEN 'Assesment and Selection' THEN 3
WHEN 'Placement' THEN 4
END) AS Phasesort
SELECT subject, regardingobjectidname, createdon, modifiedon, stat, initial,
(CASEstatWHEN 'Application Recieved' THEN 'Initial/Data Collection'
WHEN 'For Screening' THEN 'Screening'
WHEN 'Shortlisted' THEN 'Screening'
WHEN 'For Assesment' THEN 'Assesment and Selection'
WHEN 'For Assesment' THEN 'Assesment and Selection'
WHEN 'Passed Initial Interview' THEN 'Assesment and Selection'
WHEN 'Passed Profiles Assesment' THEN 'Assesment and Selection'
WHEN 'Passed Technical Exam' THEN 'Assesment and Selection'
WHEN 'For Placement' THEN 'Placement'
END) AS phase
SELECT subject, regardingobjectidname, createdon, modifiedon, initial,
(CASEinitialWHEN '1 stage' THEN 'Application Recieved'
WHEN '2 stage' THEN 'Application Recieved'
WHEN '3 stage' THEN 'For Screening'
WHEN '4 stage' THEN 'Shortlisted'
WHEN '5 stage' THEN 'For Assesment'
WHEN '6 stage' THEN 'Passed Initial Interview'
WHEN '7 stage' THEN 'Passed Profiles Assesment'
WHEN '8 stage' THEN 'Passed Technical Exam'
WHEN '9 stage' THEN 'For Placement'
WHEN '10 stage' THEN 'For Placement'
END) AS stat
SELECT subject, regardingobjectidname, createdon, modifiedon,
(CASEsubjectWHEN N'application received' THEN '1 stage'
WHEN 'process application' THEN '2 stage'
WHEN 'screen application' THEN '3 stage'
WHEN 'Phone interview' THEN '4 stage'
WHEN N'initial interview' THEN '5 stage'
WHEN 'profiles assessment' THEN '6 stage'
WHEN 'technical exam and interview' THEN '7 stage'
WHEN 'background and reference check' THEN '8 stage'
WHEN 'Job Offer' THEN '9 stage'
WHEN 'Contract Signing' THEN '10 stage'
END) AS initial
FROM(SELECT subject, regardingobjectidname, createdon, modifiedon
WHERE (subject IN ('application received', 'process application',
'screen application', 'initial interview', 'profiles assessment',
'technical exam and interview', 'background and reference check', 'job offer',
'contract signing'))
SELECT subject, regardingobjectidname, createdon, modifiedon
WHEREsubject = 'phone interview'
) AS Orion
) AS Initials
) AS Phases
) AS Stats
ORDER BY regardingobjectidname, initial

I appreciate your help.

Thanks a lot.

Your future is made by the things you are presently doing.


View 9 Replies View Related

Average By Columns

Mar 31, 2008

Here's my sample formula:

SELECT dbo.TechphonesCalibQA.ID, dbo.TechphonesCalibQA.EmpID, dbo.TechphonesCalibQA.EmpName, dbo.TechphonesCalibQA.[Level],
CASE dbo.TechphonesCalibQA.G_Branded WHEN dbo.TechphonesCalibClient.G_Branded THEN 1 ELSE 0 END AS G_Branded
FROM dbo.TechPhonesCalibClient INNER JOIN
dbo.TechphonesCalibQA ON dbo.TechPhonesCalibClient.SesID = dbo.TechphonesCalibQA.SesID

I would like to get the average of column G_branded (Result from the above formula) Any idea?


View 3 Replies View Related

Hourly Average

Aug 17, 2006

Apologies for the simplicity of the question, but it reflects mycapabilities! I have the following sample fields coming from differenttables:LocationTimeDate (timestamp)DataI need to return the average of Data per Location per HOUR.Thanks.

View 7 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved