Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Trigger To Calculate Age From Date Of Birth


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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How To Get Age From Date Of Birth
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.

Thanks

View Replies !   View Related
Selecting Date Of Birth
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 Replies !   View Related
Average Date Of Birth
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 Replies !   View Related
Birth Date 1900-01-01 00:00:00.000
Hi,all,

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?

Thanks!
Betty

View Replies !   View Related
Date Of Birth Conversion
Hello
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:-

UPDATE MyTable SET DOB = RIGHT(CHARDOB, 2) + '/' + SUBSTRING(CHARDOB,
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.
Len

View Replies !   View Related
How To Get Date Of Birth And Age In Years?
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 Replies !   View Related
Get Date Of Birth Query Problem...
hello
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 Replies !   View Related
Formatting For A Birth Date Field??
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 Replies !   View Related
Creating Birth Date From Day, Month, Year Fields
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

UPDATE DOB
SET BIRTH_DATE = (dob_year+dob_mo+dob_day)

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

View Replies !   View Related
How To Calculate Cost Of A Trigger?
I wanna used derived fields to improve select performance of my system.
How can I  calculate the trigger cost in the system?

View Replies !   View Related
A Trigger To Calculate A Due Time
Hi i am trying to make a trigger that calculates a new due time from a start time. I have a hour target like 24 hours, but the dates have an starttime and stoptime or the days could even be closed. I need it to calculate the new due datetime using the 24 hours as effective working time.

Any ideas ?

View Replies !   View Related
Calculate Maximum Value Of Childrecords In A Trigger?
I have a parent record en some child records.

Let's say the child records have a field with a value in the range 1-9

Now I want to find the maximum value in the child records.

When I have that value, I want to write this value in a field in the parent record.

1) Should I do this in a (update) trigger?

2) How do you write such a trigger in sql-express?

 

thanks Klaas

Netherlands

 

 

 

View Replies !   View Related
Calculate Date
Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.

View Replies !   View Related
Calculate Week From Date
Hi,

I want to convert a date to a weeknumber in my view.
How is this possible with SQL?

View Replies !   View Related
Calculate Weekending Date.
Can someone help me with this. I need to calculate the week endingdate of the first week of the year based upon a year provided by theuser. Is there a simpler way other than writing my own UDF?

View Replies !   View Related
Calculate Date Column
Hello,

I'm using MS SQL Server 7--

I have a meter cycle table called tblMeterCycle. It shows a schedule of customer meter read dates. How can I show all the customers that have a meter read date due; that is; when the MeterReadDate column = Now() date; or the MeterReadDate column is one day less, or one day more than the Now() date? The MeterReadDate column format is mm/dd/yy.

Any suggestions?
Thanks,
Denise

View Replies !   View Related
Calculate Workdays From A Date
This is one way you can add a number of workdays to a specific date and return a workday (monday-friday).CREATE FUNCTION [dbo].[fnAddWorkdays]
(
@StartDate DATETIME,
@DaysToAdd INT
)
RETURNS DATETIME
AS
BEGIN
RETURN@StartDate
+ CASE(@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate) - 2)) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END
+ @DaysToAdd / 5 * 7
+ @DaysToAdd % 5
+ CASE WHEN (@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate + CASE (@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate) - 2)) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END) - 2)) % 7 + @DaysToAdd % 5 > 4
THEN 2
ELSE 0
END
END

E 12°55'05.25"
N 56°04'39.16"

View Replies !   View Related
Calculate Date Field
 
I want to get the number of days pending for a perticular column. I want the out put as a integer value.
 
This is what I used for my calculated field.
But when i write this day is underlined in red. It says unrecognized identifier.
 

=DATEDIFF(Day, GETDATE(),Fields!SubmittedDate.Value)

 
If I use "day", then it is not underlined but still it does not work.
 
When I use following query, I get no error message. but still no data in the column too.

=DATEDIFF("d", Fields!SubmittedDate.Value, "GETDATE()" )
 
Can anyone tell me why is that?
 
Thanks

View Replies !   View Related
How To Calculate A Date Difference In Days
Suppose I have these two days fields 
ddold   1/1/2005 12:00:00 AM 
ddnew  2/1/2007 12:00:00 AM

How can i get the DateDifference of these two dates in days.

View Replies !   View Related
Calculate Quarterly Dates From A Start Date
What is the correct way to calculate the next quarterly dates form a given start date

dateadd("q", 1 , StartDate)

using the above it calculates what month the next quarter is in but not the actual date


if my startdate 15/01/2005 (<= dd/mm/yyyy british date)

i would like to calculate the next quartly date from the 15th of jan

would i have to used days 365 then / by 4 to split the year into 4 then use that result to just add days to the startdate to get the next date ?

i.e. quarter (1 year) = 91.25 days

View Replies !   View Related
How To Calculate Expiry Date Using Stored Procedure
Hi, i'm trying to calculate on the expiry date for product which include the start date and end date.

the product can't b displayed if the start date haven't reached the date, besides, it oso can't b displayed if it has expired.

the storedprocedure tat i've written is:

WHERE
DateDiff(day, StartDate, getDate())<=0
AND
DateDiff(day, EndDate, getDate())>=0

i wonder why it can's show the result!!! is the code correct?

View Replies !   View Related
How To Calculate The Total Days Between Open And Close Date
Hi All,
I have a table call case and case_status have two fields, date and status as below:
date                          status
04/01/2006               open
04/05/2006               closed
04/10/2006               open
04/15/2006               closed
Whenever i open and closed the case, one record is insert into the case_status table.
Now I would need to calculate the total days of the case in storeprocedure.
Anyone can help me please.
Aung

View Replies !   View Related
Calculate Number Of Working Days Back From A Date
Hi,
I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)

I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse

declare @WorkingDate as datetime
   
    SELECT     @WorkingDate=dt
    FROM         tblCalendar AS c
    WHERE     (@WorkingDays =
                          (SELECT     COUNT(*) AS Expr1
                            FROM          tblCalendar AS c2
                            WHERE      (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1)
                      AND (IsHoliday = 0)

    -- Return the result of the function
    RETURN convert(varchar(12),@WorkingDate,106)

Hope someone can help

Thanks

View Replies !   View Related
T-SQL Scritp To Calculate The Last 6 Months Of Data In A Date Field.
Hi there,

if any body out there has done this before, can you please  post the T-SQL script that calculate whatever last months of data based on a Date field.

Thanks very much,

 

Manny

View Replies !   View Related
System Date In Trigger
What is the script to insert system date in a table at insertion time using trigger.

View Replies !   View Related
[ask] How To Run A Trigger Or Store Precedure In Certain Date?
hello, i have a database that will be updated on a certain datei have a column "UPDATE_DATE" which specifies the updating date, my question is"how should i make the trigger or stored procedure runs only on the date that has been specified"thanks for the assistance 

View Replies !   View Related
How To Get A Custom Trigger To Fire On A Certain Date
I am using SQL Server 2005 database for a webbased application built on .net 2.0. Here is my situation. I have a SubmitDate and a Status field in a table. I want the trigger to fire based on the datevalue in the SubmitDate field. SubmitDate field is the last date for the user to submit an application. For eg: if the SubmitDate value is 03/10/07 , I want the trigger to fire on :01 of 03/11/07 to change the Status field value to Inactive. Is that possible? Thoughts on how to do it?

View Replies !   View Related
Date Driven Email Trigger?
Hello to a new forum. I am very impressed with the level of ability I have been seeing on this forum and all the helpful posts that are going on.

I am hoping someone can help me solve my problem. I know enough SQL to be very dangerous how to create, delete, insert, update etc... and how to build queries. Here is my dilemma I need to have a piece of scheduling software send out reminder emails based on a DATETIME field. I am running SQL Server 2005, and the database mail is already configured and can successfully send out email from the management studio interface.

Does anyone know of a tutorial or can point me in the right direction to accomplish this task. I am well versed in PHP, and am hoping I might be able to get the server to trigger a PHP script or something to this extent.

View Replies !   View Related
TRIGGER To Update Records Automatically On Specified DATE
Hi everyone i need some help on my project.

I'm using MS SQL Server 2k and I need to update records in a table when a particular date has arrived AUTOMATICALLY.

Ex:

Product A has to be updated every 45 days
Product B has to be updated every 15 days

Thank you very much.

View Replies !   View Related
Insert Date Of Today In An Additional Column In Trigger
Hi! This is my trigger and I'd like to insert the date of today in Column DeletedDate. This trigger is in tblA. tblA and tblB both had the same number of columns and same fields, but I just added another column to tblB called deletedDate and i'd like to insert the date along with the other data. Thanks!!!
Insert into tblB  SELECT* FROM Deleted

View Replies !   View Related
SQL Trigger - Email Data Based On Date Field
I need to send an email when my 'LastRunDate' field is 30 days old (i.e. It should send an email if the LastRunField = 7/21/2007).

I would need to include the matching fields in the database (i.e. MachineID, Description, etc.) then update that field to todays date.

I have a few values in the 'Frequency' field such as Daily, Monthly, Yearly. Daily would be 24 hrs, monthly 30 days, yearly 365 days from the lastrundate.

I am new to T-SQL & need a good p[lace to start.

Any sugesstions.

Thanks.

View Replies !   View Related
Can Sql Server Know When The Row In Table Saved CREATE TRIGGER Date Time On The ROW ?
hi i have question

can sql server know when the row in table Saved CREATE TRIGGER date time on the ROW ?
add new  field call "date_row_save" date+time
inside the the sql server
i need to know whan the row Saved
is it possible to do this in TRIGGER ?
TNX
 

View Replies !   View Related
Can &"Date Modified&" Col Be Automatically Updated W/o Trigger For Each Table?
Hello,
I am using SQL Server 2005 and ASP.NET 2.0. We have a very simple content management system where we have to keep track of date last modified for each row in all of our content tables. I know there's a "timestamp" datatype that is used for replication scenarios, but is there anything similar that I can use to set up a date_modified column for each of my content tables that will automatically update with GETDATE() whenever anything in a given row is updated?
Or do I have to create a date_modified column of smalldatetime datatype and write a trigger on update for EVERY single table of content that I have in the database? It seems there should be an easier way to do this than to write 20 triggers for my 20 content tables.
Thanks!

View Replies !   View Related
How To Calculate?
 
Hi all,
 
  I'm having the table in the following structure,
  UId  - int
  Pct - int
  Amt - money
  Example
  UId      Pct     Amt
   12      25       1500
   12      30       2500
   12      45       2000
 
  i want to calculate to calculate the sum of amount for the UId 12 in the following manner,
  sum of ( Amt * (Pct/100)) for UId = 12
   how to write query in sql server 2000?
   when i calculate if the value (Pct/100) < 0 that is 0.25 then it will take it as 0 so i'm getting all the values as 0.
Thanks!         
 

View Replies !   View Related
Calculate Sum Of SQL Top 10
I have an SQL statement which returns the Top 10 states with the number of visitorsSELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors    FROM [Customer] WHERE Customer.year = '2006'    GROUP BY Customer.state    ORDER BY COUNT(Customer.state) DESCSo far this is what I havestate| visitorsMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9But what i need is to calculate the total for the Visitors column in my SQL so that is like soMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9Total Top 10995279.3Total for All Years12555100I tried using the sum but I was only getting one value and not the rest...So how can i accomplish this?Thank you  

View Replies !   View Related
Calculate % Qry
Hi,
 I am trying to execute a query which calculate % on group by. here is the query
SELECT     C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))
/    (
 
SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID))    FROM         MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN                      PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN                      ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN                      CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN                      ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID              WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND                       (BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))
 
 
)*100 AS [%]
 
FROM         MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN                      PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN                      ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN                      CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN                      ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_IDWHERE     (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND                       (B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL')) GROUP BY C.USR_HIGHEST_DEGREE
My problem here it , query gives error. I would appreciate if someone can give me any idea how to go about this. I tried calculating % outside sql but still doesn't work.
Any help would be appreciate.

View Replies !   View Related
Calculate Age?
I have a DOB field in my sql table, does anyone know how to display that field as an actual age in a view or a SP?

View Replies !   View Related
How To Calculate Age
Can anyone help me with how to calculate the exact age.

Thanks in advance.

View Replies !   View Related
Calculate Mtd And Ytd.
I have a table that has a providername and totals field.
I want to be able to calculate the MTD and YTD totals for each provider.

EX. Provider MTD YTD

Bob 100.00 300.00

But I am not sure how. I can get both totals seperately, but not n the same line....
Thanks
Ron

View Replies !   View Related
How To Calculate Age Using The ID NO
I have the table with list of 1500 employees with the following headings.
Surname, Ini,title,gender,Race,IdNo,FirstName

Then I need to calculate the age of each employee but I am stuk.
Please assist me.

View Replies !   View Related
Calculate Age From DOB
Hello,

I have a date of birth column in my table and I want to calculate age based on this date. Here is the code I am trying to use in my select statement.

DateDiff ("yyyy",(a.date_of_birth),CurrentDate)

Apparently CurrentDate is not recognized by SQL. What is the best way to do this?

View Replies !   View Related
Calculate %
I want to calculate rate as percentage using code below:

SELECT OrderBy, NumShiped/Total AS Rate FROM Order

I always got Rate = 0 even thought NumShiped = 80 and Total = 100

What is problem?

View Replies !   View Related
SQL Calculate Percentage
Hello every one, I am trying to get the total percentage of a column









Regions
Workbooks Required
Workbooks Sent 
Workbooks Returned
Workbooks Complete

A
20
21
20
18

B
33
33
33
30

C
19
29
18
16

D
9
18
8
8

Totals




Thanks in advance for any tips/solutions.

View Replies !   View Related
Need To Calculate Grade Age
Need to calculate the Grade age based on the birthdate and Nov month and 30th Day of the current year.
I have a working datediff statement but I need to always but in the current year. I would like to have the statement get the current year. Then if the age is greater than xx and less than xx your age level is "xyz"

This works DateDiff("d" [Birthdate], 11/30/2004) /365.25 will return the age.

I want to replace the 2004 with a getdate yyyy so I do not need to maintain this statement.

Thanks in advance of a reply
Gary

View Replies !   View Related
How To Calculate The Midpoint
I have three fields date, low, high. I need to calculate the midpointof low and high and display it.Date,Low,High20071106,92.03,92.1320071106,88.77,88.8720071106,90.20,90.3020071106,95.21,95.3120071106,93.13,93.2320071106,91.01,91.11

View Replies !   View Related
Calculate Difference?
I am having trouble creating a sp for the following situation:

The database contains a record of the mileage of trucks in the fleet. At the end of every month, a snapshot is collected of the odometer. The data looks like this:


Truck Period Reading
1 1/31/03 55102
2 1/31/03 22852
1 2/28/03 62148
2 2/28/03 32108
1 3/31/03 69806
2 3/31/03 52763

How can I calculate the actually miles traveled during the month in a query?

TIA,

Rob

View Replies !   View Related
How To Calculate Percentage
b/w two colums?

i have two numberic columns
i want percentage of column2/column1
They are on separate table.. l am intended to creat view with my results.
thanks

View Replies !   View Related
Calculate Birthdate
I need to figure out who will be turning age 65 in april, may, or june, 2004.
What should be the code, does anyopne know?

Thanks.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved