|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
CONVERT Error MessageGreetings,
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! -- Sherwood Sherwood (Sherw***@discussions.microsoft.com) writes:
> I have a "newbie" question in relation to dates. I am trying to append There are two safe date formats in SQL Server (three in SQL 2005). The> 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)) 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, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/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. -- Show quoteSherwood "Erland Sommarskog" wrote: > Sherwood (Sherw***@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, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Sherwood (Sherw***@discussions.microsoft.com) writes:
> I was able to resolve it by using the code below. The reason it Hire someone in December, and you will get a nasty surprise.> 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)) This does work: dateadd(MONTH, 1, ebm.Hire_date) And this is what you should use. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics