dcsimg
Results 1 to 10 of 10

Thread: update query for birth year

  1. #1

    Thread Starter
    Hyperactive Member yousufkhan's Avatar
    Join Date
    Jan 2002
    Location
    India
    Posts
    492

    update query for birth year

    i have a age and dateofbirth fields in table and i have only age in the talbe
    now i wanted to update the year of birth(i know it will not be exact but somthing ) in dateofbirht field which is datetime field, days and month let it be anything only year i want

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,860

    Re: update query for birth year

    Please more clearly describe what you have and what you want.

    Also - what is your backend database?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Hyperactive Member yousufkhan's Avatar
    Join Date
    Jan 2002
    Location
    India
    Posts
    492

    Re: update query for birth year

    Sql server 2000 is backend

    i have fields age, date_of_birth in a table
    while inserting records in a table i have inserted
    only age and now i wanted to update the year part of date_of_birth field from the age value

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,860

    Re: update query for birth year

    I'm curious why you would store the age of a person in a table. Isn't that age possibly wrong the very next day?

    We calculate age with a user-defined function.

    Here's an example

    Code:
    CREATE FUNCTION dbo.GetAge_F (@DOB datetime,@Date datetime)
    RETURNS varchar(100) AS  
    BEGIN
    	Declare @RS varchar(100)
    	Declare @Age int
    
    	If IsNull(@DOB,'')='' or Isnull(@Date,'')=''
    		Set @RS=''
    	Else	
    	begin
    		If DatePart(mm,@DOB)=2 and DatePart(dd,@DOB)=29 Set @DOB=DateDiff(dd,1,@DOB)
    		Set @RS=datediff(YYYY,@DOB,@Date)
    		-CASE WHEN CAST(CONVERT(CHAR(6),@DOB,101)+CAST(DATEPART(YYYY,@Date) AS VARCHAR(4)) AS DATETIME)
    			>@Date THEN 1 ELSE 0 END
    	end
    	RETURN @RS
    END
    GO
    And then use it like this

    Code:
    Select ST.StuName,dbo.GetAge_F(ST.DOB,GetDate()),ST.Gender From...
    You can even make it a COMPUTED field using GETDATE() so that it's always based on TODAY's date.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    Hyperactive Member yousufkhan's Avatar
    Join Date
    Jan 2002
    Location
    India
    Posts
    492

    Re: update query for birth year

    i got this but i have already some records in the table for which i wanted updte the year of birth from the age that i want to do i knwo it will not be correct but the reocords which already their that should be updated

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,860

    Re: update query for birth year

    Ok - you have the AGE and you want to "correct" the YEAR in the DATE OF BIRTH - is that what you are saying?

    Could you show me a couple of rows of data that have a bad YEAR and what the AGE for that row is.

    Also could you please tell me what "date" the AGE was calculated on.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: update query for birth year

    Roughly, you can have:
    YearOfBirth = CurrentYear - Age
    If Age = 20 now then YearOfBirth = 2007 - 20 = 1987
    then add dummy day and month to get DateOfBith = 1/1/1987

  8. #8

    Thread Starter
    Hyperactive Member yousufkhan's Avatar
    Join Date
    Jan 2002
    Location
    India
    Posts
    492

    Re: update query for birth year

    Quote Originally Posted by szlamany
    Ok - you have the AGE and you want to "correct" the YEAR in the DATE OF BIRTH - is that what you are saying?

    Could you show me a couple of rows of data that have a bad YEAR and what the AGE for that row is.

    Also could you please tell me what "date" the AGE was calculated on.
    age brith_dt
    ------------------------------------
    30 1899-12-30 00:14:00.000
    29 1899-12-30 00:14:00.000
    25 1899-12-30 00:14:00.000
    22 1899-12-30 00:14:00.000

  9. #9
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: update query for birth year

    Yes, the post in number 7 would solve you issue,
    Just out of my curiosity, how these values got saved into your database..
    If an answer to your question has been helpful, then please, Rate it!

    Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.


  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,860

    Re: update query for birth year

    Quote Originally Posted by yousufkhan
    age brith_dt
    ------------------------------------
    30 1899-12-30 00:14:00.000
    29 1899-12-30 00:14:00.000
    25 1899-12-30 00:14:00.000
    22 1899-12-30 00:14:00.000
    To set the field you would do something like:

    Code:
    Update SomeTable
       Set BRITH_DT=Cast( Cast(2007-AGE as varchar(4))+'-01-01' as datetime)

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width