Convert nvarchar to date

  • Thread starter Thread starter el zorro
  • Start date Start date
E

el zorro

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
 
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)
 
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?
 
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.
 
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.
 
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)
 
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
 
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.
 
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)
 
Of course, you could also use the IsDate() function to test for a valid date
in the field. This function is available with SQL-Server 2005 and +.

--
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)


Sylvain Lafontaine said:
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 said:
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.
Click to expand...
Click to expand...
 
el zorro said:
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!
Click to expand...
 
Look up the DateSerial function in the online help: DateSerial(year, month,
day) returns a date. Use some more substrings to extract the individual date
parts and append the century to the year.
 
Back
Top