CONVERT Error Message

Greetings,
I have a "newbie" question in relation to dates. I am trying to append the
year, month, and day to one another in order to create a new date. The SQL
I
am using is below. The error message I am receiving is "The conversion of a
char data type to a datetime data type resulted in an out-of-range datetime
value." I have also tried using "CAST", but I receive a similar error
message on that as well. Any idea as to what I am doing wrong?
Convert(varchar(12),(month(ebm.Hire_Date + 30) + 1)) + '/' +
Convert(varchar(12),day(ebm.Hire_Date + 30)) + '/' +
Convert(varchar(12),year(ebm.Hire_Date + 30))
Thanks in advance!
--
SherwoodSherwood (Sherwood@.discussions.microsoft.com) writes:
> I have a "newbie" question in relation to dates. I am trying to append
> the year, month, and day to one another in order to create a new date.
> The SQL I am using is below. The error message I am receiving is "The
> conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value." I have also tried using "CAST", but I
> receive a similar error message on that as well. Any idea as to what I
> am doing wrong?
> Convert(varchar(12),(month(ebm.Hire_Date + 30) + 1)) + '/' +
> Convert(varchar(12),day(ebm.Hire_Date + 30)) + '/' +
> Convert(varchar(12),year(ebm.Hire_Date + 30))
There are two safe date formats in SQL Server (three in SQL 2005). The
most commonly used is YYYYMMDD. When you use delimited formats, it's up
to the settings how the date will be interpreted.
Anyway, I am not really sure what you want to do, but you should have a
look at the dateadd() function, that may simplify your problem.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I was able to resolve it by using the code below. The reason it initially
failed was due to the fact that I was using it in one of the conditions of a
CASE statement. The other condition had a different format and apparently
that executed first. At any rate, the code below seems to be working.
Convert(varchar(15),year(ebm.Hire_Date)) + '/' +
Convert(varchar(15),(month(ebm.Hire_date) + 1)) + '/' +
Convert(varchar(15),day(ebm.Hire_Date))
Thanks.
--
Sherwood
"Erland Sommarskog" wrote:

> Sherwood (Sherwood@.discussions.microsoft.com) writes:
> There are two safe date formats in SQL Server (three in SQL 2005). The
> most commonly used is YYYYMMDD. When you use delimited formats, it's up
> to the settings how the date will be interpreted.
> Anyway, I am not really sure what you want to do, but you should have a
> look at the dateadd() function, that may simplify your problem.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Sherwood (Sherwood@.discussions.microsoft.com) writes:
> I was able to resolve it by using the code below. The reason it
> initially failed was due to the fact that I was using it in one of the
> conditions of a CASE statement. The other condition had a different
> format and apparently that executed first. At any rate, the code below
> seems to be working.
> Convert(varchar(15),year(ebm.Hire_Date)) + '/' +
> Convert(varchar(15),(month(ebm.Hire_date) + 1)) + '/' +
> Convert(varchar(15),day(ebm.Hire_Date))
Hire someone in December, and you will get a nasty surprise.
This does work:
dateadd(MONTH, 1, ebm.Hire_date)
And this is what you should use.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx