Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MBeal
Posting Yak Master
110 Posts |
 Posted - 2004-06-23 : 18:43:40
|
| What does this tell me? How can I identify what the problem really is?The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.Warning: Null value is eliminated by an aggregate or other SET operation.MBeal |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-23 : 18:48:43
|
| It means you are trying to convert a value to datetime that isn't between January 1, 1753 through December 31, 9999, which is what datetime data type supports. Or it means your date format is incorrect. Show us an example.Tara |
 |
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-06-23 : 18:54:58
|
| I'm embarassed to show you this one. I'm trying to strip away the year, and if the month falls between 01 and 06 I want it to have a year of 2005. Otherwise, I want the year to be 2004. The case statement is rather sloppy, so I apologize in advance... There are also two other calculations -- one is a deadline of 45 days prior to the above mentioned date, and then a drop date which is 21 days earlier than that.Case WHEN DatePart(MM,s4.S_EntDate) in ('01', '02', '03', '04', '05', '06') then Cast(Cast(Datepart(MM,s4.S_EntDate) as varchar(2))+'/'+ Cast(Datepart(DD,s4.S_EntDate) as varchar(2))+'/'+'2005' as datetime) ELSE Cast(Cast(Datepart(MM,s4.S_EntDate) as varchar(2))+'/'+ Cast(Datepart(DD,s4.S_EntDate) as varchar(2))+'/'+'2004' as datetime) End [EstEntDate], DateAdd(DD, -45, Cast(Cast(Datepart(MM,s4.S_EntDate) as varchar(2))+'/'+ Cast(Datepart(DD,s4.S_EntDate) as varchar(2))+'/'+'2005' as datetime)) [DeadLine], DateAdd(DD, -66, Cast(Cast(Datepart(MM,s4.S_EntDate) as varchar(2))+'/'+ Cast(Datepart(DD,s4.S_EntDate) as varchar(2))+'/'+'2005' as datetime)) [DropDate]MBeal |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-23 : 19:02:27
|
I did not get any errors when I changed s4.S_EntDate to GETDATE(). So I think you must have a data problem. You can do the CASE like this instead:SELECT CASE WHEN DATEPART(MM,GETDATE()) BETWEEN 1 AND 6 THEN REPLACE(GETDATE(), DATEPART(yy, GETDATE()), 2005) ELSE REPLACE(GETDATE(), DATEPART(yy, GETDATE()), 2004) END [EstEntDate] DATEPART(mm, ColumnName) returns an integer value. You should not use '01'...Tara |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-06-23 : 19:04:36
|
| Thank you again!!!MBeal |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2004-06-24 : 10:06:58
|
| I just found out what it was -- I was trying to change the year, but unfortunately I didn't consider the 29th of February -- a leap year. It happened in 2004, however it doesn't happen in 2005. I was trying to change it into 02/29/2005 and that created an out of range error. I hope this helps someone else. Thank you again for all of your help on this.MBeal |
|
|
|
|
|
|
|
|
Out of range error