CAST - Substring help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CAST - Substring help

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-31 : 13:00:33

I need to seperate the Birthdate into three fields, a two digit Month, two digit Day and a 2 digit Year. When I try the month....

'502.PatientBirthMonth' = ISNULL(SUBSTRING(pp.birthdate,1,2),''),

I get this:

Server: Msg 256, Level 16, State 1, Line 1
The data type datetime is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

SELECT '290.PatientName'=IsNull(pp.First,'') + ' ' + isnull(pp.Middle,'') + ' ' + isnull(pp.Last,'')+ ' ' + isnull(pp.Suffix,''),
'291.PatLast'=IsNull(pp.Last,''),
'292.PatFirst'=IsNull(pp.First,''),
'293.PatMiddle'=IsNull(pp.Middle,''),
'294.PatientAddr1'=IsNull(pp.Address1,''),
'295.PatientAddr2'=IsNull(pp.Address2,''),
'296.PatientCity'=IsNull(pp.City,''),
'297.PatientState'=IsNull(pp.State,''),
'298.PatientZip'=IsNull(pp.Zip,''),
'299.PatientCountry' = ISNULL(pp.Country,''),
'300.PatientBirthdate' = pp.Birthdate,
'502.PatientBirthMonth' = ISNULL(SUBSTRING(pp.birthdate,1,2),''),
'301.PatientSex'=IsNull(pp.Sex,''),
'302.PatientPhone1'=IsNull(pp.Phone1,''),
'303.PatientSSN'=IsNull(pp.SSN,''),
'304.PatOccupation'=IsNull(pp.EmpOccup,''),
'305.PatSchool'=IsNull(pp.SchoolName,''),
'306.PatBudget'=IsNull(g.Budget,0),
'307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0),
'308.PatSuffix'=IsNull(pp.Suffix,''),
'500.PatientPhone1AreaCode' = ISNULL(SUBSTRING(pp.phone1,1,3),''),
'501.PatientPhone1Number'= ISNULL(SUBSTRING(pp.phone1,4,3),'')+ '-' + SUBSTRING(pp.phone1,7,4)

FROM PatientProfile pp
LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
WHERE pp.PatientProfileID = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-31 : 13:04:46

use ISNULL(SUSBTRING(CAST(pp.birthdate AS varchar(11)),1,2,'')....

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-31 : 13:07:04

Then this is returned:

Server: Msg 174, Level 15, State 1, Line 12
The substring function requires 3 arguments.

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-31 : 13:08:56

'502.PatientBirthMonth' = ISNULL(SUBSTRING(CAST(pp.birthdate AS varchar(11)),1,2),''),

This returns the month in varchar --- "Ma" for May. I need this to read as "05"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-31 : 13:18:17

I'm assuming you're storing these as varchar and not int

Jim

DECLARE @d datetime
DECLARE @month varchar(2)
DECLARE @year varchar(4)
DECLARE @day varchar(2)

SET @d = current_timestamp

SELECT @d

SET @month = RIGHT('0'+CONVERT(Varchar(2),MONTH(@d)),2 )
SET @day = RIGHT('0'+CONVERT(varchar(2),DAY(@d)) ,2 )
SET @year = RIGHT(CONVERT(varchar(4),YEAR(@d)) ,2 )

SELECT @d,@month,@day,@year

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-31 : 13:20:52

I tried this:

'502.PatientBirthMonth' = ISNULL(DATEPART(MONTH,pp.birthdate),''),

however only got my date as "5". Is it possible to get this as "05" ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-31 : 13:24:17

CASE WHEN DATEPART(month,pp.birthdate)>10 THEN CAST(DATEPART(month,pp.birthdate) AS varchar(2)) ELSE CAST('0'+ DATEPART(month,pp.birthdate) AS varchar(2)) for month

RIGHT(CAST(DATEPART(yy,pp.birthdate) AS varchar(2)),2) for year

CASE WHEN DATEPART(dd,pp.birthdate)>10 THEN CAST(DATEPART(dd,pp.birthdate) AS varchar(2)) ELSE CAST('0'+ DATEPART(dd,pp.birthdate) AS varchar(2)) for day

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-31 : 13:33:03

When I try your suggestion, I get another syntax error.

'502.PatientBirthMonth' =
CASE
WHEN DATEPART(month,pp.birthdate)> 10 THEN CAST(DATEPART(month,pp.birthdate) AS varchar(2))
ELSE CAST('0' + DATEPART(month,pp.birthdate) AS varchar(2)),

Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near ','.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-31 : 13:38:59

quote:
Originally posted by JeffS23

When I try your suggestion, I get another syntax error.

'502.PatientBirthMonth' =
CASE
WHEN DATEPART(month,pp.birthdate)> 10 THEN CAST(DATEPART(month,pp.birthdate) AS varchar(2))
ELSE CAST('0' + DATEPART(month,pp.birthdate) AS varchar(2))
END,

Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near ','.

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-31 : 14:05:48

For a patient born in May, this still gives me "5" not "05". Any thoughts?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-31 : 14:14:22

'502.PatientBirthMonth' = RIGHT('0'+CONVERT(varchar(2),DAY(birthdate)) ,2 )

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-31 : 14:44:03

FYI, this is what I ended up using:

'502.PatientBirthMonth' =
CASE
WHEN DATEPART(month,pp.birthdate) <= 9 then cast('0' + cast(datepart(month,pp.birthdate) as varchar(1)) as varchar(2))
ELSE CAST(datepart(month,pp.birthdate) as varchar(2))
END,
'503.PatientBirthDays' =
CASE
WHEN DATEPART(dd,pp.birthdate) <= 9 then cast('0' + cast(datepart(dd,pp.birthdate) as varchar(1)) as varchar(2))
ELSE CAST(datepart(dd,pp.birthdate) as varchar(2))
END,
'504.PatientBirthYear' = RIGHT(cast(datepart(year,pp.birthdate) as varchar(4)),2),

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources