| Author |
Topic |
|
gibson_hg
Starting Member
3 Posts |
 Posted - 2009-02-02 : 08:42:14
|
| Hello all,On Friday I ran a query that I run at the end of the month. The query checks for a date range, but in order to do that it has to build a date string to check. The table I query does not have a date column but rather it has month, day and year columns.Here is the query so you can see how it works: select sum(flighthours) as "Flight Hours"from flt f join mission m on f.mseq=m.mseqwhere convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))between '01-jan-2009' and '31-jan-2009'For some reason this stopped working and gives this error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.I have researched the net I have made sure that I am not using an invalid date format. I have tried different formats as well. This query, and many others that use this where claus, worked for the better part of 6 years.I did not write them but I have been using these for 15 months without issue. I also checked the regional settings on the SQL Server and my local PC as well. I run the query from my machine using Enterprise Manager and Query Analyzer pointed to the SQL Server. My colleague also does this and it stopped working for him as well.The regional settings are set for US English, the date is also correct on the SQL Server. Anybody else have this issue? Any thoughts on what might be causing this? The machine has had no recent updates, it's an NT4 box and therefore receives no updates/patches.Thanks in advance for the help. |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-02 : 08:46:48
|
| i think ur f.yr or f.mn are datecolumnsand having values as dd/mm/yyyy format u have to follow yyyy/mm/dd or mm/dd/yyyy formatcheck these queriesdeclare @dd datetime select @dd = '14/6/2008'select @dd, convert(varchar(32),@dd)declare @dd datetime select @dd = '6/14/2008'select @dd, convert(varchar(32),@dd) |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-02-02 : 08:50:32
|
| I would guess that f.mn, f.dy or f.yr contains some bad data. Check the max and min values in each of these columns and check there is not a month/day or year that would cause SQL to throw this error. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-02 : 09:01:02
|
Maybe not bad data, but regional setting interpreting dmy instead of mdy.Try to use ISO format, like thiswhere cast(f.yr AS varchar(4)) + '-' + cast(f.mn AS varchar(2)) + '-' + cast(f.dy AS varchar(2))
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-02 : 09:02:15
|
Hmmm... Above string could be a problem in German language setting.Try this if above failsWHERE REPLACE(STR(f.yr, 4) + STR(f.mn, 2) + STR(f.dy, 2), ' ', '0')
E 12°55'05.63"N 56°04'39.26" |
|
|
|
gibson_hg
Starting Member
3 Posts |
Posted - 2009-02-02 : 09:23:28
|
| The yr, mn and dy columns are numeric. I tried running the query for a date range that I know that works, so I don't see it being bad data.The regional settings are US English also. The yr, mn and dy columns must be read in as datetime so I can check the date range. |
|
|
|
gibson_hg
Starting Member
3 Posts |
Posted - 2009-02-02 : 09:36:48
|
| Hi all,Thanks for the quick replies but I got it working after. I took RickD's advice and ran the min and max for each and one record had 81 for the day, thanks to a corrupted upload on Friday.Thanks again everyone for your help. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-02-02 : 10:02:45
|
| No Problem. This happens too easily when you don't store dates as dates. |
|
|
|
|
Out-of-range datetime value