ASP.NET

Get Birthdate RSS

7 replies

Last post Apr 07, 2012 07:37 AM by sandeepmittal11

‹ Previous Thread|Next Thread ›

- Reply
dieseldave

Member

384 Points

434 Posts
Get Birthdate

Apr 06, 2012 10:15 AM|LINK
The table has first and last names but no ID.

It has a birthdate column which is null for all but it has an age column. How can I update the birthdate? This returns a syntax error:
```
update dbo.Sheet1$BikeSebring24Hours021912_1
set dob  =   CAST(CAST( DATEPART(yyyy, GETDATE() ) - sb.age  FROM Sheet1$BikeSebring24Hours021912_1 sb  WHERE lname = sb.lname and fname = sb.fname)  AS VARCHAR(4))  + '-01-01' as datetime)
```
DieselDave
- Reply
sandeepmitta...

Contributor

6801 Points

1059 Posts

Re: Get Birthdate

Apr 06, 2012 11:09 AM|LINK

I believe with only age you would not be able to find birthdate. you would only be able to find the year but not exact date.

Sandeep Mittal | My Blog - IT Developer Zone
- Reply
tdmca

Contributor

2396 Points

661 Posts

Re: Get Birthdate

Apr 06, 2012 11:17 AM|LINK

select dob=DATEADD(yy,-24,'2012-11-10')

or

select dob=DATEADD(yy,-age,'2012-11-10') from ages

see -ve sign before age column

If you feel helped. Please mark this post as Answer
- Edited by tdmca on Apr 06, 2012 11:19 AM
- Reply
dieseldave

Member

384 Points

434 Posts

Re: Get Birthdate

Apr 06, 2012 12:58 PM|LINK

Sorry, I did not explain. for our purposes everyones birthday is January 1, so all I need to do is get the year and add it to 1/1.

DieselDave
- Reply
tdmca

Contributor

2396 Points

661 Posts

Re: Get Birthdate

Apr 06, 2012 01:13 PM|LINK

ok execute this. it is just an idea that how to get dob

select dob=DATEADD(yy,-age,'2012-1-1') from ages

If you feel helped. Please mark this post as Answer
- Edited by tdmca on Apr 06, 2012 01:13 PM
- Reply
gimimex

Participant

1052 Points

157 Posts
Re: Get Birthdate

Apr 06, 2012 01:20 PM|LINK
Try something like that:
```
update dbo.Sheet1$BikeSebring24Hours021912_1
set dob = CAST( CAST( DATEPART(yyyy, GETDATE()) - sb.age AS VARCHAR(4)) + '-01-01' as datetime)
FROM Sheet1$BikeSebring24Hours021912_1 sb
WHERE lname = sb.lname and fname = sb.fname)
```
I hope this helps.
http://www.imoveisemexposicao.com.br/imoveis-em-guarulhos
- Marked as answer by Chen Yu - MSFT on Apr 11, 2012 06:39 AM
- Reply
limno

All-Star

117340 Points

8005 Posts

Moderator
MVP
Re: Get Birthdate

Apr 06, 2012 01:50 PM|LINK
```
update sb
set dob  =  DATEADD(yy,-age,DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
FROM Sheet1$BikeSebring24Hours021912_1 sb
```
Limno

Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
- Marked as answer by Chen Yu - MSFT on Apr 11, 2012 06:39 AM
- Reply
sandeepmitta...

Contributor

6801 Points

1059 Posts
Re: Get Birthdate

Apr 07, 2012 07:37 AM|LINK
```
UPDATE dbo.Sheet1$BikeSebring24Hours021912_1
SET dob = '01/01/' + CAST(YEAR(GETDATE()) - age AS VARCHAR(4))
```
Sandeep Mittal | My Blog - IT Developer Zone