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 |
|
Westley
Posting Yak Master
229 Posts |
 Posted - 2006-03-31 : 01:39:24
|
| Hi all, Just wondering, does anyone have a datetime problem before, here is what I had:A table have a datetime field which contains: 7641-10-22 1985:33:31.520, that stored as a valid datetime, have no idea how it gets in, but when I do this:convert(varchar(50), field1, 121) the date becomes: 7641-10-22 28:13:00.800 which is still wrong, as the hour becomes a very funny hour, has anyone seen this before?Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-31 : 02:13:05
|
Have not seen this before.Possibly due to data corruption ?
KHChoice is an illusion, created between those with power, and those without. |
 |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-03-31 : 02:35:37
|
| Not too sure myself, I can't re-produce this issue, as in I can't insert a date with 4 digits hour, I have no idea how it was done in the first place. The data was from sybase bcp the data out then bcp in to SQL (where SQL 2000 was on sp4), I check the data on sybase, and it was fine (as in the large year with normal hour 2 digits).and if that is a data corruption, shouldn't checktable/checkdb detect it? Would be interesting to know how it got there in the first place. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-03-31 : 03:48:08
|
| Interesting, I didn't come across that post, but when I did the IsDate(field1)=0, nothing returns, which means that they are valid date. I tried to cast it to a float then cast it back to a datetime, it somehow work itself out to be a valid date format. But the strange thing is, from the source data, the date part is correct (as in 7641-10-22), only having the funny hour. |
|
|
|
|
|
|
|
|
Funny date