Converting CHAR data type to a date/time data type in a T-SQL select statement

Question

Post reply

Converting CHAR data type to a date/time data type in a T-SQL select statement

Austin Moran

Old Hand

Points: 375
More actions
October 3, 2005 at 8:08 am

#91341

I am attempting to write a select statement which requires that I convert one of the fields from a CHAR data type to a date/time data type. I am unsure how to accomplish this. I would very much appreciate any help you guys might offer! The "birth_date" field for some reason was set up as a CHAR data type field and I need to convert it to a DATE type field in order to ascertain the age of members. Here's theh select statement that I've been fighting with:
Declare @total Decimal (8,2)
Select @total = convert(Decimal (8,2), Count (*)) from dbo.Member_Association
WHERE (Status = 'A') AND
(Primary_Indicator = 'P') AND (Member_Type_Code = 'R' OR
Member_Type_Code = 'RA')
SELECT 'Under 18 years of age' AS Age,
COUNT(*) AS 'NumberOfMembers', '1' as 'orderby',
(convert(DECIMAL (8,2),Count(*))/@Total * 100) AS 'Percentage'
FROM dbo.Member_Association, dbo.Member
WHERE (Status = 'A') AND (Primary_Indicator = 'P') AND
(Member_Type_Code = 'R' OR
Member_Type_Code = 'RA') AND
(Member.Birth_Date BETWEEN DATEADD(year, - 1, GETDATE()) AND GETDATE())
I am a complete NOVICE at this, so there is surely a better way to do this. I appreciate any and all advice or guidance. Thanks in advance.
- Austin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Austin - it would be helpful if you could provide some samples from your "birth_date" column...

You may be able to do something like this..substituting the dates with the name of your column...

SELECT age = DATEDIFF(year, cast('01/01/1992' as smalldatetime), cast('01/01/2005' as smalldatetime))

**ASCII stupid question, get a stupid ANSI !!!**

Austin Moran Old Hand Points: 375 More actions · Answer 2

Thanks for your response! Here are some records from the Birth_Date field:

19791231

19791230

19791229

19791226

NOTE: Many of the Birth_Date fields are populated with nothing but zeroes.

Thanks again for taking the time to help me out with this. I have spent much more time that I am willing to admit in public on what i thought would be a relatively simple thing. - Austin

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

I can only guess this may be what you want since you do not know which values will contain zeroes and how to process those records...

SELECT CASE WHEN Member.Birth_Date = '00000000'

THEN 'Member Birthdate Unknown'

ELSE 'Under 18 years of age'

END AS Age,

COUNT(*) AS 'NumberOfMembers',

'1' AS 'orderby',

(CONVERT( decimal(8, 2), COUNT(*)) / @Total * 100) AS 'Percentage'

FROM dbo.Member_Association, dbo.Member -- unknown how you are joining these two tables...

WHERE (Status = 'A')

AND (Primary_Indicator = 'P')

AND( Member_Type_Code = 'R' OR Member_Type_Code = 'RA')

AND( SELECT CONVERT( smalldatetime, Member.Birth_Date) BETWEEN DATEADD( year, - 1, GETDATE()) AND GETDATE()

OR Member.Birth_Date = '00000000')

I wasn't born stupid - I had to study.

Sergiy SSC Guru Points: 110208 More actions · Answer 4

SELECT case when ISDATE(Member.Birth_Date) = 1 then CONVERT( smalldatetime, Member.Birth_Date) else 0 end ...

_____________
Code for TallyGenerator