SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Convert varchar to date
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

bosoxfan
Starting Member

2 Posts

Posted - 12/29/2006 :  10:00:09  Show Profile
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

USA
292 Posts

Posted - 12/29/2006 :  10:47:25  Show Profile
Convert(datetime, DOB + '/' + Convert(varchar, (Year(GetDate()))))
Go to Top of Page

bosoxfan
Starting Member

2 Posts

Posted - 12/29/2006 :  11:06:31  Show Profile
That does the trick! Thanks a bunch.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 01/01/2007 :  04:46:26  Show Profile  Send madhivanan a Yahoo! Message
1 You should always store dates in proper DATETIME datatype
2 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 DATETIME
4 The suggested method wont work if the date format of the server is different than MDY


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/01/2007 :  12:38:23  Show Profile  Visit Seventhnight's Homepage
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 ..."
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 01/02/2007 :  06:18:00  Show Profile  Send madhivanan a Yahoo! Message
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...


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


Thanks Corey. I didnt know that

Where were you for long time?
Still relocation?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/02/2007 :  09:06:31  Show Profile  Visit Seventhnight's Homepage
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

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 ..."
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/02/2007 :  09:30:23  Show Profile  Visit jsmith8858's Homepage
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000