convert datetime if... then... else...

Hi!
Apreciates any help.
I have a 9 digit nvarchar identification where i'm using following code
ddmmyy123. I wish to exctract the ddmmyy to a datetime BirthDate. The
problem is if someone is born 14. march 1905 the code shows 140305. When
converting the nvarchar to datetime it shows 14. march 2005. Ref script
example:
SELECT [ID], Convert (SmallDateTime, (Subtring([ID], 5, 2) + Subtring([ID],
3, 2) Subtring([ID], 1, 2)), 112) AS BirthDate FROM [MyPatientList]
My case is that the numbers 123 indicates in wich century the patient is
born. If the number 123 is in the range 0-500 the person is born in the 190
0
century, else he/she is born in the 2000 century.
I wish to use following code, but I'm missing something. What' please hel
p.
UPDATE MyPatientList
IF (CONVERT (INTEGER, SUBSTRING([ID], 7, 3) ) <= 500) THEN
SET BirthDate = CONVERT (SMALLDATETIME, (CONVERT (VARCHAR (2), 19 ) +
SUBSTRING([ID], 5, 2) + SUBSTRING([ID], 3, 2) + SUBSTRING([ID], 1, 2)), 112)
ELSE
SET BirthDate = CONVERT (SMALLDATETIME, (CONVERT (VARCHAR (2), 20 ) +
SUBSTRING([ID], 5, 2) + SUBSTRING([ID], 3, 2) + SUBSTRING([ID], 1, 2)), 112)
Best Regards
Hans ?rsj?Hi,
try this:
update MyPatientList
set BirthDate = case when CONVERT (INTEGER, SUBSTRING([ID], 7, 3) ) <= 500
then
dateadd(year, -100, Convert (SmallDateTime, (Substring(ID, 5, 2) +
Substring(ID, 3, 2) +
Substring(ID, 1, 2)), 112))
else
Convert (SmallDateTime, (Substring(ID, 5, 2) + Substring(ID, 3, 2) +
Substring(ID, 1, 2)), 112)
end
and this is a test:
declare @.ID varchar(20)
set @.ID = '140305625'
SELECT @.ID, case when convert(int, substring(@.ID, 7, 3)) <= 500 then
dateadd(year, -100, Convert (SmallDateTime, (Substring(@.ID, 5, 2) +
Substring(@.ID, 3, 2) +
Substring(@.ID, 1, 2)), 112)) else Convert (SmallDateTime, (Substring(@.ID, 5,
2) + Substring(@.ID, 3, 2) +
Substring(@.ID, 1, 2)), 112)
end AS BirthDate
set @.ID = '140305123'
SELECT @.ID, case when convert(int, substring(@.ID, 7, 3)) < =500 then
dateadd(year, -100, Convert (SmallDateTime, (Substring(@.ID, 5, 2) +
Substring(@.ID, 3, 2) +
Substring(@.ID, 1, 2)), 112)) else Convert (SmallDateTime, (Substring(@.ID, 5,
2) + Substring(@.ID, 3, 2) +
Substring(@.ID, 1, 2)), 112)
end AS BirthDate
HTH
Peter|||Hi Rogas69!!!
I think that will work. Thank you :-)
/Hans
"Rogas69" wrote:

> Hi,
> try this:
> update MyPatientList
> set BirthDate = case when CONVERT (INTEGER, SUBSTRING([ID], 7, 3) ) <= 500
> then
> dateadd(year, -100, Convert (SmallDateTime, (Substring(ID, 5, 2) +
> Substring(ID, 3, 2) +
> Substring(ID, 1, 2)), 112))
> else
> Convert (SmallDateTime, (Substring(ID, 5, 2) + Substring(ID, 3, 2) +
> Substring(ID, 1, 2)), 112)
> end
>
> and this is a test:
>
> declare @.ID varchar(20)
> set @.ID = '140305625'
> SELECT @.ID, case when convert(int, substring(@.ID, 7, 3)) <= 500 then
> dateadd(year, -100, Convert (SmallDateTime, (Substring(@.ID, 5, 2) +
> Substring(@.ID, 3, 2) +
> Substring(@.ID, 1, 2)), 112)) else Convert (SmallDateTime, (Substring(@.ID,
5,
> 2) + Substring(@.ID, 3, 2) +
> Substring(@.ID, 1, 2)), 112)
> end AS BirthDate
> set @.ID = '140305123'
> SELECT @.ID, case when convert(int, substring(@.ID, 7, 3)) < =500 then
> dateadd(year, -100, Convert (SmallDateTime, (Substring(@.ID, 5, 2) +
> Substring(@.ID, 3, 2) +
> Substring(@.ID, 1, 2)), 112)) else Convert (SmallDateTime, (Substring(@.ID,
5,
> 2) + Substring(@.ID, 3, 2) +
> Substring(@.ID, 1, 2)), 112)
> end AS BirthDate
>
> HTH
> Peter
>
>|||I forgot to ask on my first reply.
Is it possible to have multiple "case" 's before the final "else"?
/Hans
"Rogas69" wrote:

> Hi,
> try this:
> update MyPatientList
> set BirthDate = case when CONVERT (INTEGER, SUBSTRING([ID], 7, 3) ) <= 500
> then
> dateadd(year, -100, Convert (SmallDateTime, (Substring(ID, 5, 2) +
> Substring(ID, 3, 2) +
> Substring(ID, 1, 2)), 112))
> else
> Convert (SmallDateTime, (Substring(ID, 5, 2) + Substring(ID, 3, 2) +
> Substring(ID, 1, 2)), 112)
> end
>
> and this is a test:
>
> declare @.ID varchar(20)
> set @.ID = '140305625'
> SELECT @.ID, case when convert(int, substring(@.ID, 7, 3)) <= 500 then
> dateadd(year, -100, Convert (SmallDateTime, (Substring(@.ID, 5, 2) +
> Substring(@.ID, 3, 2) +
> Substring(@.ID, 1, 2)), 112)) else Convert (SmallDateTime, (Substring(@.ID,
5,
> 2) + Substring(@.ID, 3, 2) +
> Substring(@.ID, 1, 2)), 112)
> end AS BirthDate
> set @.ID = '140305123'
> SELECT @.ID, case when convert(int, substring(@.ID, 7, 3)) < =500 then
> dateadd(year, -100, Convert (SmallDateTime, (Substring(@.ID, 5, 2) +
> Substring(@.ID, 3, 2) +
> Substring(@.ID, 1, 2)), 112)) else Convert (SmallDateTime, (Substring(@.ID,
5,
> 2) + Substring(@.ID, 3, 2) +
> Substring(@.ID, 1, 2)), 112)
> end AS BirthDate
>
> HTH
> Peter
>
>|||sure. check BOL for the CASE syntax.
Peter
"Hans - DiaGraphIT -" <HansDiaGraphIT@.discussions.microsoft.com> wrote in
message news:96A12BAC-31B5-4659-A9C8-899E1F84881B@.microsoft.com...
>I forgot to ask on my first reply.
> Is it possible to have multiple "case" 's before the final "else"?
>