| Author |
Topic |
|
bosoxfan
Starting Member
2 Posts |
 Posted - 2006-12-29 : 10:00:09
|
| I need some help converting a varchar column to datetime. I have varchar field that stores a person's birthdate in mm/dd format, because no one wants anyone else to know their birth year. In my query, I want to know if anyone is celebrating a birthday in the current month, so where birthdate is between 1/1/07 and 1/31/07, and so on. How do I convert my birthdate column to datetime using the current year, so I can make the comparison? |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-12-29 : 10:47:25
|
| Convert(datetime, DOB + '/' + Convert(varchar, (Year(GetDate())))) |
 |
|
|
bosoxfan
Starting Member
2 Posts |
Posted - 2006-12-29 : 11:06:31
|
| That does the trick! Thanks a bunch. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-01-01 : 04:46:26
|
| 1 You should always store dates in proper DATETIME datatype2 If you dont want to store the year how will you calculate their age?3 Using varchar will make your life difficulty as you need to convert it to DATETIME4 The suggested method wont work if the date format of the server is different than MDYMadhivananFailing to plan is Planning to fail |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-01 : 12:38:23
|
Madhi - in the US, companies often don't want to keep actual DOB, especially pre-hiring. They don't want to be accused of age discrimination...Corey Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."  |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-01-02 : 06:18:00
|
quote:
Originally posted by Seventhnight
Madhi - in the US, companies often don't want to keep actual DOB, especially pre-hiring. They don't want to be accused of age discrimination...CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."
Thanks Corey. I didnt know that  Where were you for long time?Still relocation?MadhivananFailing to plan is Planning to fail |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2007-01-02 : 09:06:31
|
Started at new job this morning...I last semester I had school, job, side job, pregnant wife, and a 2 year old.... so I was pretty well used up  CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-02 : 09:30:23
|
quote:
Originally posted by bosoxfan
I need some help converting a varchar column to datetime. I have varchar field that stores a person's birthdate in mm/dd format, because no one wants anyone else to know their birth year. In my query, I want to know if anyone is celebrating a birthday in the current month, so where birthdate is between 1/1/07 and 1/31/07, and so on. How do I convert my birthdate column to datetime using the current year, so I can make the comparison?
I would use two tinyint columns to store this information, if the year is not required -- BirthDay and BirthMonth. This will give you better data integrity than you have now, with no parsing and no reliance on the format in which the data is stored. Whenever you notice that a single column contains two or more peices of data in it in a particular format, that is a good indicator that it should be broken out into either multiple columns (as in this case) or normalized into multiple rows in a related table.- Jeff |
|
|
|
|
Convert varchar to date