I'm thinking this may not be posssible, but...

An alphanumeric ID Number that I have in my ADP/SQL database contains within
it 6 characters representing the birth date. So of you were born today, your
ID would include 010510, as in mmddyy.

I can extract those 6 edigits using SUBSTRING(), but I can't seem to get
them to convert to a date data type (so I can test for the age of the
person). As an experiment, I used a today's date in this expression:

CONVERT (nvarchar, CONVERT (DATETIME, '010510', 1))

and got "May 10 2001 12:00AM" as the output. Obviously, the function did
not read my string as mmddyy, but as yymmdd. What I want is for the output to
be "01/05/10." Is this possible?

Thanks!

(I tried to post an earlier version of this question here, but I don't see.
I apologize if it shows up somewhere.)

THanks!

After you extract the substrings, put them back together in the correct
order for SQL Server to interpret it as a date. Look in Books On-Line for
SQL Server's date interpretations for details. I usually use 'yyyy.mm.dd'
which always seems to be interpreted correctly, but there are other choices
that may depend on the connection's Set DateFormat setting. BOL says that a
numeric string interpretation is ALWAYS yymmdd, and yyyymmdd is also
allowed, and the DateFormat is ignored.

So I think something like this would do what you want:
Cast(substring(ID,5,2)+substring(ID,1,4) as smalldatetime) as BirthDate

"el zorro" <(E-Mail Removed)> wrote in message
news:B93B5A35-3C7A-480C-97D6-(E-Mail Removed)...
> I'm thinking this may not be posssible, but...
>
> An alphanumeric ID Number that I have in my ADP/SQL database contains
> within
> it 6 characters representing the birth date. So of you were born today,
> your
> ID would include 010510, as in mmddyy.
>
> I can extract those 6 edigits using SUBSTRING(), but I can't seem to get
> them to convert to a date data type (so I can test for the age of the
> person). As an experiment, I used a today's date in this expression:
>
> CONVERT (nvarchar, CONVERT (DATETIME, '010510', 1))
>
> and got "May 10 2001 12:00AM" as the output. Obviously, the function did
> not read my string as mmddyy, but as yymmdd. What I want is for the output
> to
> be "01/05/10." Is this possible?

The format with any separator is always the ISO format, which mean it always
begins with the year, followed by the month and then the day; so you'll have
either yymmdd or yyyymmdd.

Move the last two characters at the front and you should be ok:

Select CONVERT (DATETIME, right ('010510', 2) + left ('010510', 4))

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"el zorro" <(E-Mail Removed)> wrote in message
news:B93B5A35-3C7A-480C-97D6-(E-Mail Removed)...
> I'm thinking this may not be posssible, but...
>
> An alphanumeric ID Number that I have in my ADP/SQL database contains
> within
> it 6 characters representing the birth date. So of you were born today,
> your
> ID would include 010510, as in mmddyy.
>
> I can extract those 6 edigits using SUBSTRING(), but I can't seem to get
> them to convert to a date data type (so I can test for the age of the
> person). As an experiment, I used a today's date in this expression:
>
> CONVERT (nvarchar, CONVERT (DATETIME, '010510', 1))
>
> and got "May 10 2001 12:00AM" as the output. Obviously, the function did
> not read my string as mmddyy, but as yymmdd. What I want is for the output
> to
> be "01/05/10." Is this possible?
>
> Thanks!
>
> (I tried to post an earlier version of this question here, but I don't
> see.
> I apologize if it shows up somewhere.)
>
> THanks!

That was a great Idea, Paul!

Just one little wrinkle to iron out. (I haven't tried the CONVERT approach
suggested by Sylvain yet, but probably the same issue will arise.) It
converts 122662 correctly as 2/26/1962, but 021530 incorrectly as 2/15/2030
(instead of 2/15/1930). Yes, we have some old folks in the database...

I can probably program my way around this, maybe some kind of IIF statement
that if the date is greater than today, then subtract 100 years... what do
you think?



"Paul Shapiro" wrote:

> After you extract the substrings, put them back together in the correct
> order for SQL Server to interpret it as a date. Look in Books On-Line for
> SQL Server's date interpretations for details. I usually use 'yyyy.mm.dd'
> which always seems to be interpreted correctly, but there are other choices
> that may depend on the connection's Set DateFormat setting. BOL says that a
> numeric string interpretation is ALWAYS yymmdd, and yyyymmdd is also
> allowed, and the DateFormat is ignored.
>
> So I think something like this would do what you want:
> Cast(substring(ID,5,2)+substring(ID,1,4) as smalldatetime) as BirthDate
>
> "el zorro" <(E-Mail Removed)> wrote in message
> news:B93B5A35-3C7A-480C-97D6-(E-Mail Removed)...
> > I'm thinking this may not be posssible, but...
> >
> > An alphanumeric ID Number that I have in my ADP/SQL database contains
> > within
> > it 6 characters representing the birth date. So of you were born today,
> > your
> > ID would include 010510, as in mmddyy.
> >
> > I can extract those 6 edigits using SUBSTRING(), but I can't seem to get
> > them to convert to a date data type (so I can test for the age of the
> > person). As an experiment, I used a today's date in this expression:
> >
> > CONVERT (nvarchar, CONVERT (DATETIME, '010510', 1))
> >
> > and got "May 10 2001 12:00AM" as the output. Obviously, the function did
> > not read my string as mmddyy, but as yymmdd. What I want is for the output
> > to
> > be "01/05/10." Is this possible?
>
> .
>

One of SQL Server's settings is how you want it to interpret 2-digit dates.
The default setting is 50, meaning dates below 50 are assumed to be in the
current century and above 50 are assumed to be in the last century. You can
look into changing that setting, but I am pretty sure it's server-wide, so
you might do better with your own logic.

The better solution is to NEVER store 2-digit years, and even better is to
use a date data-type for storing dates. It might be easiest to just correct
the structure now and keep life simpler for the future. Using a birthdate as
an ID is prone to failure, because you don't need that many people to reach
a conflict. One pair of twins would definitely be a problem, but even
unrelated people can have the same birthday.

"el zorro" <(E-Mail Removed)> wrote in message
news1C847EA-5250-49FC-9AE2-(E-Mail Removed)...
> That was a great Idea, Paul!
>
> Just one little wrinkle to iron out. (I haven't tried the CONVERT approach
> suggested by Sylvain yet, but probably the same issue will arise.) It
> converts 122662 correctly as 2/26/1962, but 021530 incorrectly as
> 2/15/2030
> (instead of 2/15/1930). Yes, we have some old folks in the database...
>
> I can probably program my way around this, maybe some kind of IIF
> statement
> that if the date is greater than today, then subtract 100 years... what do
> you think?
>
>
> "Paul Shapiro" wrote:
>
>> After you extract the substrings, put them back together in the correct
>> order for SQL Server to interpret it as a date. Look in Books On-Line for
>> SQL Server's date interpretations for details. I usually use 'yyyy.mm.dd'
>> which always seems to be interpreted correctly, but there are other
>> choices
>> that may depend on the connection's Set DateFormat setting. BOL says that
>> a
>> numeric string interpretation is ALWAYS yymmdd, and yyyymmdd is also
>> allowed, and the DateFormat is ignored.
>>
>> So I think something like this would do what you want:
>> Cast(substring(ID,5,2)+substring(ID,1,4) as smalldatetime) as BirthDate
>>
>> "el zorro" <(E-Mail Removed)> wrote in message
>> news:B93B5A35-3C7A-480C-97D6-(E-Mail Removed)...
>> > I'm thinking this may not be posssible, but...
>> >
>> > An alphanumeric ID Number that I have in my ADP/SQL database contains
>> > within
>> > it 6 characters representing the birth date. So of you were born today,
>> > your
>> > ID would include 010510, as in mmddyy.
>> >
>> > I can extract those 6 edigits using SUBSTRING(), but I can't seem to
>> > get
>> > them to convert to a date data type (so I can test for the age of the
>> > person). As an experiment, I used a today's date in this expression:
>> >
>> > CONVERT (nvarchar, CONVERT (DATETIME, '010510', 1))
>> >
>> > and got "May 10 2001 12:00AM" as the output. Obviously, the function
>> > did
>> > not read my string as mmddyy, but as yymmdd. What I want is for the
>> > output
>> > to be "01/05/10." Is this possible?

THanks Paul-- Yes, it appears that the server is using the 50 year default. I
appreciate your point about using birthdates in the ID, but that's beyond my
control.

SO I've tried to make the adjustment by using a logical construct to test
for dates that are greater than today and subtracting 100 years, but it's
not working as I had hoped. I had planned to use an IIF statement, but I
guess SQL doesn't use that. So I tried CASE WHEN:

CASE WHEN CAST(SUBSTRING(data, 7 , 2) + SUBSTRING(data , 3 , 4) AS
smalldatetime) > GETDATE() THEN DATEADD(year , 100, CAST(SUBSTRING(data, 7 ,
2) + SUBSTRING(data, 3 , 4) AS smalldatetime)) ELSE CAST(SUBSTRING(data, 7 ,
2) + SUBSTRING(data , 3 , 4) AS smalldatetime) END

I've tested this and can see that it works as far as testing the date
against today's date, but the THEN and ELSE statments crash (syntax error
converting character string to smalldatetime data type). If I substitute text
for them, such as OVER and OK, it works fine.

I'm trying to fit all this into a SQL SELECT statement for a view, so maybe
I'm completely on the wrong track. What do you think?

Thanks again.






"Paul Shapiro" wrote:

> One of SQL Server's settings is how you want it to interpret 2-digit dates.
> The default setting is 50, meaning dates below 50 are assumed to be in the
> current century and above 50 are assumed to be in the last century. You can
> look into changing that setting, but I am pretty sure it's server-wide, so
> you might do better with your own logic.
>
> The better solution is to NEVER store 2-digit years, and even better is to
> use a date data-type for storing dates. It might be easiest to just correct
> the structure now and keep life simpler for the future. Using a birthdate as
> an ID is prone to failure, because you don't need that many people to reach
> a conflict. One pair of twins would definitely be a problem, but even
> unrelated people can have the same birthday.
>
> "el zorro" <(E-Mail Removed)> wrote in message
> news1C847EA-5250-49FC-9AE2-(E-Mail Removed)...
> > That was a great Idea, Paul!
> >
> > Just one little wrinkle to iron out. (I haven't tried the CONVERT approach
> > suggested by Sylvain yet, but probably the same issue will arise.) It
> > converts 122662 correctly as 2/26/1962, but 021530 incorrectly as
> > 2/15/2030
> > (instead of 2/15/1930). Yes, we have some old folks in the database...
> >
> > I can probably program my way around this, maybe some kind of IIF
> > statement
> > that if the date is greater than today, then subtract 100 years... what do
> > you think?
> >
> >
> > "Paul Shapiro" wrote:
> >
> >> After you extract the substrings, put them back together in the correct
> >> order for SQL Server to interpret it as a date. Look in Books On-Line for
> >> SQL Server's date interpretations for details. I usually use 'yyyy.mm.dd'
> >> which always seems to be interpreted correctly, but there are other
> >> choices
> >> that may depend on the connection's Set DateFormat setting. BOL says that
> >> a
> >> numeric string interpretation is ALWAYS yymmdd, and yyyymmdd is also
> >> allowed, and the DateFormat is ignored.
> >>
> >> So I think something like this would do what you want:
> >> Cast(substring(ID,5,2)+substring(ID,1,4) as smalldatetime) as BirthDate
> >>
> >> "el zorro" <(E-Mail Removed)> wrote in message
> >> news:B93B5A35-3C7A-480C-97D6-(E-Mail Removed)...
> >> > I'm thinking this may not be posssible, but...
> >> >
> >> > An alphanumeric ID Number that I have in my ADP/SQL database contains
> >> > within
> >> > it 6 characters representing the birth date. So of you were born today,
> >> > your
> >> > ID would include 010510, as in mmddyy.
> >> >
> >> > I can extract those 6 edigits using SUBSTRING(), but I can't seem to
> >> > get
> >> > them to convert to a date data type (so I can test for the age of the
> >> > person). As an experiment, I used a today's date in this expression:
> >> >
> >> > CONVERT (nvarchar, CONVERT (DATETIME, '010510', 1))
> >> >
> >> > and got "May 10 2001 12:00AM" as the output. Obviously, the function
> >> > did
> >> > not read my string as mmddyy, but as yymmdd. What I want is for the
> >> > output
> >> > to be "01/05/10." Is this possible?
>
> .
>

The cut about the number 50 is not about the current year but it's an
absolute, so I don't know why you are using the CurrentDate() here to make
your test and there is also no need to convert the whole string to a
smalldatetime for performing the test: you should make the test only over
the integer value returned by the two characters.

Second and more important, you are not subtracting 100 years at this time,
you are adding it; hence your overflow with smalldatetime for some values.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"el zorro" <(E-Mail Removed)> wrote in message
news:6729F815-CEE1-4FF9-B7F0-(E-Mail Removed)...
> THanks Paul-- Yes, it appears that the server is using the 50 year
> default. I
> appreciate your point about using birthdates in the ID, but that's beyond
> my
> control.
>
> SO I've tried to make the adjustment by using a logical construct to test
> for dates that are greater than today and subtracting 100 years, but it's
> not working as I had hoped. I had planned to use an IIF statement, but I
> guess SQL doesn't use that. So I tried CASE WHEN:
>
> CASE WHEN CAST(SUBSTRING(data, 7 , 2) + SUBSTRING(data , 3 , 4) AS
> smalldatetime) > GETDATE() THEN DATEADD(year , 100, CAST(SUBSTRING(data, 7
> ,
> 2) + SUBSTRING(data, 3 , 4) AS smalldatetime)) ELSE CAST(SUBSTRING(data, 7
> ,
> 2) + SUBSTRING(data , 3 , 4) AS smalldatetime) END
>
> I've tested this and can see that it works as far as testing the date
> against today's date, but the THEN and ELSE statments crash (syntax error
> converting character string to smalldatetime data type). If I substitute
> text
> for them, such as OVER and OK, it works fine.
>
> I'm trying to fit all this into a SQL SELECT statement for a view, so
> maybe
> I'm completely on the wrong track. What do you think?
>
> Thanks again.
>
>
>
>
>
>
> "Paul Shapiro" wrote:
>
>> One of SQL Server's settings is how you want it to interpret 2-digit
>> dates.
>> The default setting is 50, meaning dates below 50 are assumed to be in
>> the
>> current century and above 50 are assumed to be in the last century. You
>> can
>> look into changing that setting, but I am pretty sure it's server-wide,
>> so
>> you might do better with your own logic.
>>
>> The better solution is to NEVER store 2-digit years, and even better is
>> to
>> use a date data-type for storing dates. It might be easiest to just
>> correct
>> the structure now and keep life simpler for the future. Using a birthdate
>> as
>> an ID is prone to failure, because you don't need that many people to
>> reach
>> a conflict. One pair of twins would definitely be a problem, but even
>> unrelated people can have the same birthday.
>>
>> "el zorro" <(E-Mail Removed)> wrote in message
>> news1C847EA-5250-49FC-9AE2-(E-Mail Removed)...
>> > That was a great Idea, Paul!
>> >
>> > Just one little wrinkle to iron out. (I haven't tried the CONVERT
>> > approach
>> > suggested by Sylvain yet, but probably the same issue will arise.) It
>> > converts 122662 correctly as 2/26/1962, but 021530 incorrectly as
>> > 2/15/2030
>> > (instead of 2/15/1930). Yes, we have some old folks in the database...
>> >
>> > I can probably program my way around this, maybe some kind of IIF
>> > statement
>> > that if the date is greater than today, then subtract 100 years... what
>> > do
>> > you think?
>> >
>> >
>> > "Paul Shapiro" wrote:
>> >
>> >> After you extract the substrings, put them back together in the
>> >> correct
>> >> order for SQL Server to interpret it as a date. Look in Books On-Line
>> >> for
>> >> SQL Server's date interpretations for details. I usually use
>> >> 'yyyy.mm.dd'
>> >> which always seems to be interpreted correctly, but there are other
>> >> choices
>> >> that may depend on the connection's Set DateFormat setting. BOL says
>> >> that
>> >> a
>> >> numeric string interpretation is ALWAYS yymmdd, and yyyymmdd is also
>> >> allowed, and the DateFormat is ignored.
>> >>
>> >> So I think something like this would do what you want:
>> >> Cast(substring(ID,5,2)+substring(ID,1,4) as smalldatetime) as
>> >> BirthDate
>> >>
>> >> "el zorro" <(E-Mail Removed)> wrote in message
>> >> news:B93B5A35-3C7A-480C-97D6-(E-Mail Removed)...
>> >> > I'm thinking this may not be posssible, but...
>> >> >
>> >> > An alphanumeric ID Number that I have in my ADP/SQL database
>> >> > contains
>> >> > within
>> >> > it 6 characters representing the birth date. So of you were born
>> >> > today,
>> >> > your
>> >> > ID would include 010510, as in mmddyy.
>> >> >
>> >> > I can extract those 6 edigits using SUBSTRING(), but I can't seem to
>> >> > get
>> >> > them to convert to a date data type (so I can test for the age of
>> >> > the
>> >> > person). As an experiment, I used a today's date in this expression:
>> >> >
>> >> > CONVERT (nvarchar, CONVERT (DATETIME, '010510', 1))
>> >> >
>> >> > and got "May 10 2001 12:00AM" as the output. Obviously, the
>> >> > function
>> >> > did
>> >> > not read my string as mmddyy, but as yymmdd. What I want is for the
>> >> > output
>> >> > to be "01/05/10." Is this possible?
>>
>> .
>>

Try this version. It pre-pends the '19' century to the year part of the
string when the assumed century results in a birthdate greater than today,
forcing the resulting date to be in the 1900's.

Declare @ID char(6)
Set @ID='010710'
Select
@ID as Input

,Cast(substring(@ID,5,2)+substring(@ID,1,4) as datetime) as
BirthDateWithGuessedCentury

,Case When Cast(substring(@ID,5,2)+substring(@ID,1,4) as datetime) >
GETDATE()
Then
Cast('19' + substring(@ID,5,2)+substring(@ID,1,4) as datetime)
Else
Cast(substring(@ID,5,2)+substring(@ID,1,4) as datetime)
End as BirthDateWithCorrectedCentury

"el zorro" <(E-Mail Removed)> wrote in message
news:6729F815-CEE1-4FF9-B7F0-(E-Mail Removed)...
> THanks Paul-- Yes, it appears that the server is using the 50 year
> default. I
> appreciate your point about using birthdates in the ID, but that's beyond
> my
> control.
>
> SO I've tried to make the adjustment by using a logical construct to test
> for dates that are greater than today and subtracting 100 years, but it's
> not working as I had hoped. I had planned to use an IIF statement, but I
> guess SQL doesn't use that. So I tried CASE WHEN:
>
> CASE WHEN CAST(SUBSTRING(data, 7 , 2) + SUBSTRING(data , 3 , 4) AS
> smalldatetime) > GETDATE() THEN DATEADD(year , 100, CAST(SUBSTRING(data, 7
> ,
> 2) + SUBSTRING(data, 3 , 4) AS smalldatetime)) ELSE CAST(SUBSTRING(data, 7
> ,
> 2) + SUBSTRING(data , 3 , 4) AS smalldatetime) END
>
> I've tested this and can see that it works as far as testing the date
> against today's date, but the THEN and ELSE statments crash (syntax error
> converting character string to smalldatetime data type). If I substitute
> text
> for them, such as OVER and OK, it works fine.
>
> I'm trying to fit all this into a SQL SELECT statement for a view, so
> maybe
> I'm completely on the wrong track. What do you think?
>
> Thanks again.
>
>
> "Paul Shapiro" wrote:
>
>> One of SQL Server's settings is how you want it to interpret 2-digit
>> dates.
>> The default setting is 50, meaning dates below 50 are assumed to be in
>> the
>> current century and above 50 are assumed to be in the last century. You
>> can
>> look into changing that setting, but I am pretty sure it's server-wide,
>> so
>> you might do better with your own logic.
>>
>> The better solution is to NEVER store 2-digit years, and even better is
>> to
>> use a date data-type for storing dates. It might be easiest to just
>> correct
>> the structure now and keep life simpler for the future. Using a birthdate
>> as
>> an ID is prone to failure, because you don't need that many people to
>> reach
>> a conflict. One pair of twins would definitely be a problem, but even
>> unrelated people can have the same birthday.
>>
>> "el zorro" <(E-Mail Removed)> wrote in message
>> news1C847EA-5250-49FC-9AE2-(E-Mail Removed)...
>> > That was a great Idea, Paul!
>> >
>> > Just one little wrinkle to iron out. (I haven't tried the CONVERT
>> > approach
>> > suggested by Sylvain yet, but probably the same issue will arise.) It
>> > converts 122662 correctly as 2/26/1962, but 021530 incorrectly as
>> > 2/15/2030
>> > (instead of 2/15/1930). Yes, we have some old folks in the database...
>> >
>> > I can probably program my way around this, maybe some kind of IIF
>> > statement
>> > that if the date is greater than today, then subtract 100 years... what
>> > do
>> > you think?
>> >
>> >
>> > "Paul Shapiro" wrote:
>> >
>> >> After you extract the substrings, put them back together in the
>> >> correct
>> >> order for SQL Server to interpret it as a date. Look in Books On-Line
>> >> for
>> >> SQL Server's date interpretations for details. I usually use
>> >> 'yyyy.mm.dd'
>> >> which always seems to be interpreted correctly, but there are other
>> >> choices
>> >> that may depend on the connection's Set DateFormat setting. BOL says
>> >> that
>> >> a
>> >> numeric string interpretation is ALWAYS yymmdd, and yyyymmdd is also
>> >> allowed, and the DateFormat is ignored.
>> >>
>> >> So I think something like this would do what you want:
>> >> Cast(substring(ID,5,2)+substring(ID,1,4) as smalldatetime) as
>> >> BirthDate
>> >>
>> >> "el zorro" <(E-Mail Removed)> wrote in message
>> >> news:B93B5A35-3C7A-480C-97D6-(E-Mail Removed)...
>> >> > I'm thinking this may not be posssible, but...
>> >> >
>> >> > An alphanumeric ID Number that I have in my ADP/SQL database
>> >> > contains
>> >> > within
>> >> > it 6 characters representing the birth date. So of you were born
>> >> > today,
>> >> > your
>> >> > ID would include 010510, as in mmddyy.
>> >> >
>> >> > I can extract those 6 edigits using SUBSTRING(), but I can't seem to
>> >> > get
>> >> > them to convert to a date data type (so I can test for the age of
>> >> > the
>> >> > person). As an experiment, I used a today's date in this expression:
>> >> >
>> >> > CONVERT (nvarchar, CONVERT (DATETIME, '010510', 1))
>> >> >
>> >> > and got "May 10 2001 12:00AM" as the output. Obviously, the
>> >> > function
>> >> > did
>> >> > not read my string as mmddyy, but as yymmdd. What I want is for the
>> >> > output
>> >> > to be "01/05/10." Is this possible?

Thanks for your response, Sylvain.

I tried testing based on the last 2 numbers in the string, but I couldn't
come up with anything that worked. Ideally I would like to just find a
logical expression that says "if the date is greater than today, which can't
be real since it's a birth date, then subtract 100 years." Yes, you are right
about the 100 in my DATEADD expression-- that should be "-100"

I almost have the thing working, but when I apply it to a very large
dataset, say, 75,000 records, I tend to get an error message that says
"Syntax error converting datetime from character string" and the whole thing
basically crashes.

I think the issue is data entry errors that have the wrong number of
characters in the ID, or maybe letters where there should be numbers, or who
knows what. Is there a function to test for proper formatting and data before
I convert the ID string to extract the date characters?

Again, In in ADP/SQL, and tryinmg to write a SELECT SQL statement to do all
this.

Thanks.


"Sylvain Lafontaine" wrote:

> The cut about the number 50 is not about the current year but it's an
> absolute, so I don't know why you are using the CurrentDate() here to make
> your test and there is also no need to convert the whole string to a
> smalldatetime for performing the test: you should make the test only over
> the integer value returned by the two characters.
>
> Second and more important, you are not subtracting 100 years at this time,
> you are adding it; hence your overflow with smalldatetime for some values.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "el zorro" <(E-Mail Removed)> wrote in message
> news:6729F815-CEE1-4FF9-B7F0-(E-Mail Removed)...
> > THanks Paul-- Yes, it appears that the server is using the 50 year
> > default. I
> > appreciate your point about using birthdates in the ID, but that's beyond
> > my
> > control.
> >
> > SO I've tried to make the adjustment by using a logical construct to test
> > for dates that are greater than today and subtracting 100 years, but it's
> > not working as I had hoped. I had planned to use an IIF statement, but I
> > guess SQL doesn't use that. So I tried CASE WHEN:
> >
> > CASE WHEN CAST(SUBSTRING(data, 7 , 2) + SUBSTRING(data , 3 , 4) AS
> > smalldatetime) > GETDATE() THEN DATEADD(year , 100, CAST(SUBSTRING(data, 7
> > ,
> > 2) + SUBSTRING(data, 3 , 4) AS smalldatetime)) ELSE CAST(SUBSTRING(data, 7
> > ,
> > 2) + SUBSTRING(data , 3 , 4) AS smalldatetime) END
> >
> > I've tested this and can see that it works as far as testing the date
> > against today's date, but the THEN and ELSE statments crash (syntax error
> > converting character string to smalldatetime data type). If I substitute
> > text
> > for them, such as OVER and OK, it works fine.
> >
> > I'm trying to fit all this into a SQL SELECT statement for a view, so
> > maybe
> > I'm completely on the wrong track. What do you think?
> >
> > Thanks again.
> >
> >
> >
> >
> >
> >
> > "Paul Shapiro" wrote:
> >
> >> One of SQL Server's settings is how you want it to interpret 2-digit
> >> dates.
> >> The default setting is 50, meaning dates below 50 are assumed to be in
> >> the
> >> current century and above 50 are assumed to be in the last century. You
> >> can
> >> look into changing that setting, but I am pretty sure it's server-wide,
> >> so
> >> you might do better with your own logic.
> >>
> >> The better solution is to NEVER store 2-digit years, and even better is
> >> to
> >> use a date data-type for storing dates. It might be easiest to just
> >> correct
> >> the structure now and keep life simpler for the future. Using a birthdate
> >> as
> >> an ID is prone to failure, because you don't need that many people to
> >> reach
> >> a conflict. One pair of twins would definitely be a problem, but even
> >> unrelated people can have the same birthday.
> >>
> >> "el zorro" <(E-Mail Removed)> wrote in message
> >> news1C847EA-5250-49FC-9AE2-(E-Mail Removed)...
> >> > That was a great Idea, Paul!
> >> >
> >> > Just one little wrinkle to iron out. (I haven't tried the CONVERT
> >> > approach
> >> > suggested by Sylvain yet, but probably the same issue will arise.) It
> >> > converts 122662 correctly as 2/26/1962, but 021530 incorrectly as
> >> > 2/15/2030
> >> > (instead of 2/15/1930). Yes, we have some old folks in the database...
> >> >
> >> > I can probably program my way around this, maybe some kind of IIF
> >> > statement
> >> > that if the date is greater than today, then subtract 100 years... what
> >> > do
> >> > you think?
> >> >
> >> >
> >> > "Paul Shapiro" wrote:
> >> >
> >> >> After you extract the substrings, put them back together in the
> >> >> correct
> >> >> order for SQL Server to interpret it as a date. Look in Books On-Line
> >> >> for
> >> >> SQL Server's date interpretations for details. I usually use
> >> >> 'yyyy.mm.dd'
> >> >> which always seems to be interpreted correctly, but there are other
> >> >> choices
> >> >> that may depend on the connection's Set DateFormat setting. BOL says
> >> >> that
> >> >> a
> >> >> numeric string interpretation is ALWAYS yymmdd, and yyyymmdd is also
> >> >> allowed, and the DateFormat is ignored.
> >> >>
> >> >> So I think something like this would do what you want:
> >> >> Cast(substring(ID,5,2)+substring(ID,1,4) as smalldatetime) as
> >> >> BirthDate
> >> >>
> >> >> "el zorro" <(E-Mail Removed)> wrote in message
> >> >> news:B93B5A35-3C7A-480C-97D6-(E-Mail Removed)...
> >> >> > I'm thinking this may not be posssible, but...
> >> >> >
> >> >> > An alphanumeric ID Number that I have in my ADP/SQL database
> >> >> > contains
> >> >> > within
> >> >> > it 6 characters representing the birth date. So of you were born
> >> >> > today,
> >> >> > your
> >> >> > ID would include 010510, as in mmddyy.
> >> >> >
> >> >> > I can extract those 6 edigits using SUBSTRING(), but I can't seem to
> >> >> > get
> >> >> > them to convert to a date data type (so I can test for the age of
> >> >> > the
> >> >> > person). As an experiment, I used a today's date in this expression:
> >> >> >
> >> >> > CONVERT (nvarchar, CONVERT (DATETIME, '010510', 1))
> >> >> >
> >> >> > and got "May 10 2001 12:00AM" as the output. Obviously, the
> >> >> > function
> >> >> > did
> >> >> > not read my string as mmddyy, but as yymmdd. What I want is for the
> >> >> > output
> >> >> > to be "01/05/10." Is this possible?
> >>
> >> .
> >>
>
>
> .
>

Your syntax error may come for invalid characters but also for invalid
number values, such as a month above 12. There is also the possibility of
null values but this shouldn't generate this kind of error.

You should add in the WHERE statement a LIKE statement to test for the
acceptance of proper values only; something like:

select * into #t from (select 'xx090230' as data union all select
'xx130230') as q

select CASE WHEN CAST(SUBSTRING(data, 7 , 2) + SUBSTRING(data , 3 , 4) AS
smalldatetime) > GETDATE() THEN DATEADD(year , -100, CAST(SUBSTRING(data, 7
,
2) + SUBSTRING(data, 3 , 4) AS smalldatetime)) ELSE CAST(SUBSTRING(data, 7 ,
2) + SUBSTRING(data , 3 , 4) AS smalldatetime) END
from #t

where ( (substring(data,3,1)='0' and substring(data,4,1) like '[0-9]')
or (substring(data,3,1)='1' and substring(data,4,1) like '[012]') )
---- AND (...)

drop table #t

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"el zorro" <(E-Mail Removed)> wrote in message
news:5C3FBAA8-72CE-411E-910B-(E-Mail Removed)...
> Thanks for your response, Sylvain.
>
> I tried testing based on the last 2 numbers in the string, but I couldn't
> come up with anything that worked. Ideally I would like to just find a
> logical expression that says "if the date is greater than today, which
> can't
> be real since it's a birth date, then subtract 100 years." Yes, you are
> right
> about the 100 in my DATEADD expression-- that should be "-100"
>
> I almost have the thing working, but when I apply it to a very large
> dataset, say, 75,000 records, I tend to get an error message that says
> "Syntax error converting datetime from character string" and the whole
> thing
> basically crashes.
>
> I think the issue is data entry errors that have the wrong number of
> characters in the ID, or maybe letters where there should be numbers, or
> who
> knows what. Is there a function to test for proper formatting and data
> before
> I convert the ID string to extract the date characters?
>
> Again, In in ADP/SQL, and tryinmg to write a SELECT SQL statement to do
> all
> this.
>
> Thanks.
>
>
> "Sylvain Lafontaine" wrote:
>
>> The cut about the number 50 is not about the current year but it's an
>> absolute, so I don't know why you are using the CurrentDate() here to
>> make
>> your test and there is also no need to convert the whole string to a
>> smalldatetime for performing the test: you should make the test only over
>> the integer value returned by the two characters.
>>
>> Second and more important, you are not subtracting 100 years at this
>> time,
>> you are adding it; hence your overflow with smalldatetime for some
>> values.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server
>> (French)
>>
>>
>> "el zorro" <(E-Mail Removed)> wrote in message
>> news:6729F815-CEE1-4FF9-B7F0-(E-Mail Removed)...
>> > THanks Paul-- Yes, it appears that the server is using the 50 year
>> > default. I
>> > appreciate your point about using birthdates in the ID, but that's
>> > beyond
>> > my
>> > control.
>> >
>> > SO I've tried to make the adjustment by using a logical construct to
>> > test
>> > for dates that are greater than today and subtracting 100 years, but
>> > it's
>> > not working as I had hoped. I had planned to use an IIF statement, but
>> > I
>> > guess SQL doesn't use that. So I tried CASE WHEN:
>> >
>> > CASE WHEN CAST(SUBSTRING(data, 7 , 2) + SUBSTRING(data , 3 , 4) AS
>> > smalldatetime) > GETDATE() THEN DATEADD(year , 100,
>> > CAST(SUBSTRING(data, 7
>> > ,
>> > 2) + SUBSTRING(data, 3 , 4) AS smalldatetime)) ELSE
>> > CAST(SUBSTRING(data, 7
>> > ,
>> > 2) + SUBSTRING(data , 3 , 4) AS smalldatetime) END
>> >
>> > I've tested this and can see that it works as far as testing the date
>> > against today's date, but the THEN and ELSE statments crash (syntax
>> > error
>> > converting character string to smalldatetime data type). If I
>> > substitute
>> > text
>> > for them, such as OVER and OK, it works fine.
>> >
>> > I'm trying to fit all this into a SQL SELECT statement for a view, so
>> > maybe
>> > I'm completely on the wrong track. What do you think?
>> >
>> > Thanks again.
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Paul Shapiro" wrote:
>> >
>> >> One of SQL Server's settings is how you want it to interpret 2-digit
>> >> dates.
>> >> The default setting is 50, meaning dates below 50 are assumed to be in
>> >> the
>> >> current century and above 50 are assumed to be in the last century.
>> >> You
>> >> can
>> >> look into changing that setting, but I am pretty sure it's
>> >> server-wide,
>> >> so
>> >> you might do better with your own logic.
>> >>
>> >> The better solution is to NEVER store 2-digit years, and even better
>> >> is
>> >> to
>> >> use a date data-type for storing dates. It might be easiest to just
>> >> correct
>> >> the structure now and keep life simpler for the future. Using a
>> >> birthdate
>> >> as
>> >> an ID is prone to failure, because you don't need that many people to
>> >> reach
>> >> a conflict. One pair of twins would definitely be a problem, but even
>> >> unrelated people can have the same birthday.
>> >>
>> >> "el zorro" <(E-Mail Removed)> wrote in message
>> >> news1C847EA-5250-49FC-9AE2-(E-Mail Removed)...
>> >> > That was a great Idea, Paul!
>> >> >
>> >> > Just one little wrinkle to iron out. (I haven't tried the CONVERT
>> >> > approach
>> >> > suggested by Sylvain yet, but probably the same issue will arise.)
>> >> > It
>> >> > converts 122662 correctly as 2/26/1962, but 021530 incorrectly as
>> >> > 2/15/2030
>> >> > (instead of 2/15/1930). Yes, we have some old folks in the
>> >> > database...
>> >> >
>> >> > I can probably program my way around this, maybe some kind of IIF
>> >> > statement
>> >> > that if the date is greater than today, then subtract 100 years...
>> >> > what
>> >> > do
>> >> > you think?
>> >> >
>> >> >
>> >> > "Paul Shapiro" wrote:
>> >> >
>> >> >> After you extract the substrings, put them back together in the
>> >> >> correct
>> >> >> order for SQL Server to interpret it as a date. Look in Books
>> >> >> On-Line
>> >> >> for
>> >> >> SQL Server's date interpretations for details. I usually use
>> >> >> 'yyyy.mm.dd'
>> >> >> which always seems to be interpreted correctly, but there are other
>> >> >> choices
>> >> >> that may depend on the connection's Set DateFormat setting. BOL
>> >> >> says
>> >> >> that
>> >> >> a
>> >> >> numeric string interpretation is ALWAYS yymmdd, and yyyymmdd is
>> >> >> also
>> >> >> allowed, and the DateFormat is ignored.
>> >> >>
>> >> >> So I think something like this would do what you want:
>> >> >> Cast(substring(ID,5,2)+substring(ID,1,4) as smalldatetime) as
>> >> >> BirthDate
>> >> >>
>> >> >> "el zorro" <(E-Mail Removed)> wrote in message
>> >> >> news:B93B5A35-3C7A-480C-97D6-(E-Mail Removed)...
>> >> >> > I'm thinking this may not be posssible, but...
>> >> >> >
>> >> >> > An alphanumeric ID Number that I have in my ADP/SQL database
>> >> >> > contains
>> >> >> > within
>> >> >> > it 6 characters representing the birth date. So of you were born
>> >> >> > today,
>> >> >> > your
>> >> >> > ID would include 010510, as in mmddyy.
>> >> >> >
>> >> >> > I can extract those 6 edigits using SUBSTRING(), but I can't seem
>> >> >> > to
>> >> >> > get
>> >> >> > them to convert to a date data type (so I can test for the age of
>> >> >> > the
>> >> >> > person). As an experiment, I used a today's date in this
>> >> >> > expression:
>> >> >> >
>> >> >> > CONVERT (nvarchar, CONVERT (DATETIME, '010510', 1))
>> >> >> >
>> >> >> > and got "May 10 2001 12:00AM" as the output. Obviously, the
>> >> >> > function
>> >> >> > did
>> >> >> > not read my string as mmddyy, but as yymmdd. What I want is for
>> >> >> > the
>> >> >> > output
>> >> >> > to be "01/05/10." Is this possible?
>> >>
>> >> .
>> >>
>>
>>
>> .
>>