Convert(datetime, '07/11/48') Returns Year 2048!

Hello all! I'm having some issues trying to convert a varchar string
into datetime.
What I'm wanting to do is take the string '07/11/48' and convert it to
datetime, i.e.: 1948-07-11 00:00:00.000, but the convert function is
returning 2048 as the year! The function I'm using is:
SELECT CONVERT(DateTime, '07/11/48') AS Birth_Date
In addition to the wrong year, I'm also getting an error when trying to
apply the 101 style to the above function. The error is, "Syntax error
converting datetime from character string," and all I'm doing is adding
the ", 101" style number into the function like this:
SELECT CONVERT(DateTime, '07/11/48', 101) AS Birth_Date
I've searched and searched the groups and various other places and
can't find the solution. Can someone please help?!
Thank you =)Direct from BOL for SQL 2000:
(search CONVERT; it's the first hit).
Quote:
"By default, SQL Server interprets two-digit years based on a cutoff year of
2049. That is, the two-digit year 49 is interpreted as 2049 and the
two-digit year 50 is interpreted as 1950. Many client applications, such as
those based on OLE Automation objects, use a cutoff year of 2030. SQL Server
provides a configuration option (two digit year cutoff) that changes the
cutoff year used by SQL Server and allows the consistent treatment of dates.
The safest course, however, is to specify four-digit years."
James Hokes
<crferguson@.gmail.com> wrote in message
news:1147963833.086980.150920@.38g2000cwa.googlegroups.com...
> Hello all! I'm having some issues trying to convert a varchar string
> into datetime.
> What I'm wanting to do is take the string '07/11/48' and convert it to
> datetime, i.e.: 1948-07-11 00:00:00.000, but the convert function is
> returning 2048 as the year! The function I'm using is:
> SELECT CONVERT(DateTime, '07/11/48') AS Birth_Date
> In addition to the wrong year, I'm also getting an error when trying to
> apply the 101 style to the above function. The error is, "Syntax error
> converting datetime from character string," and all I'm doing is adding
> the ", 101" style number into the function like this:
> SELECT CONVERT(DateTime, '07/11/48', 101) AS Birth_Date
> I've searched and searched the groups and various other places and
> can't find the solution. Can someone please help?!
> Thank you =)
>|||Try Books Online, under the CONVERT syntax:
Important By default, SQL Server interprets two-digit years based on a
cutoff year of 2049. That is, the two-digit year 49 is interpreted as
2049 and the two-digit year 50 is interpreted as 1950. Many client
applications, such as those based on OLE Automation objects, use a
cutoff year of 2030. SQL Server provides a configuration option (two
digit year cutoff) that changes the cutoff year used by SQL Server and
allows the consistent treatment of dates. The safest course, however,
is to specify four-digit years.
HTH,
Stu|||Thanks both of you! I'm going to have to find a work-around for it
then as I can't change the configuration on the SQL server. Also, I'm
going to have to find the books online you both quoted!|||crferguson@.gmail.com wrote on 18 May 2006 07:50:33 -0700:

> Hello all! I'm having some issues trying to convert a varchar string
> into datetime.
> What I'm wanting to do is take the string '07/11/48' and convert it to
> datetime, i.e.: 1948-07-11 00:00:00.000, but the convert function is
> returning 2048 as the year! The function I'm using is:
> SELECT CONVERT(DateTime, '07/11/48') AS Birth_Date
> In addition to the wrong year, I'm also getting an error when trying to
> apply the 101 style to the above function. The error is, "Syntax error
> converting datetime from character string," and all I'm doing is adding
> the ", 101" style number into the function like this:
> SELECT CONVERT(DateTime, '07/11/48', 101) AS Birth_Date
> I've searched and searched the groups and various other places and
> can't find the solution. Can someone please help?!
> Thank you =)
In addition to the the other replies, the reason for the syntax error is
that 101 requires a 4 digit year, and you're passing in 2 digits. When
converting to a DateTime, the style value defines the format of the data
being converted to the DateTime.
Dan|||Thanks both of you! I'm going to have to find a work-around for it
then as I can't change the configuration on the SQL server. Also, I'm
going to have to find the books online you both quoted!|||Yeah, I feel dumb =P I was alreadly looking at the books online not
realizing that's what it was called. I skipped right over the
"Important!" note about the date cutoff... =/|||Ahh! Thanks Dan! I wasn't aware of the 4 digit requirement. I really
need to read more about these things before I fumble around so much,
but I'm on such a tight time crunch that I didn't have time to do more
than skim through everything. Thanks again for your help everyone!|||You can change it using CASE:
DECLARE @.Date varchar(8)
SET @.Date = '07/11/01'
SELECT CASE WHEN YEAR(@.Date) > YEAR(GETDATE())
THEN DATEADD(yyyy, -100, CONVERT(smalldatetime, @.Date))
ELSE CONVERT(smalldatetime, @.Date) END
untested.
The idea is that you are looking at people who are less than 100 years
old, and they couldn't have been born in the future.
HTH,
Stu|||crferguson@.gmail.com wrote on 18 May 2006 08:10:32 -0700:

> Ahh! Thanks Dan! I wasn't aware of the 4 digit requirement. I really
> need to read more about these things before I fumble around so much,
> but I'm on such a tight time crunch that I didn't have time to do more
> than skim through everything. Thanks again for your help everyone!
Go to the BOL entry for CONVERT, find 101 in the style column, look at the
top of the column where it specifies it's need a 4 digit year (yyyy), look
to the column to the left to find the style value for a 2 digit year (yy).
:)
Dansqlsql