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 |
|
X002548
Not Just a Number
15586 Posts |
 Posted - 2003-11-19 : 10:05:44
|
I can't believe this. I have a developer telling that this Nightmare project I got has the VB cod ein access ONLY pulling a Time from a datetime column....which of course has to be changed...I told nope, it's stores as 8 bytes...4 for the date and 4 for the time..no miracles...right?BUT:USE NorthwindGOCREATE TABLE myTable99 (Col1 datetime)GOINSERT INTO myTable99 (Col1) SELECT '10:00:00'INSERT INTO myTable99 (Col1) SELECT '1899-12-30 10:00:00'SELECT * FROM myTable99GODROP TABLE myTable99GO If you go in to EM (Remove the drop of course), you'll see only the time displayed...which is what he's saying he gets in vb...But in the SELECT you see the date, and in the first we get the default date 1900-01-01...Huh?Is this true? And if so, is there a "Magic" Time that only returns the Date?I'm soooo confused....Anyone seen this before?Oh, the Rocket Scientist is no longer with us....Is it a hole or a design of SQL Server?Brett8-)EDIT: In this thread, Rob mention zero time as 1899-12-30I though it was 1900-01-01SELECT CONVERT(datetime,0,101)And this is the only other referemce...on ALL of SQLTeam to that date...http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20961&SearchTerms=1899-12-30Is this a Known "cheat"? |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-11-19 : 10:30:05
|
| i think it's just how EM is configured to display that particular date. it's the default way of storing just a time. It's truly just a presentation thing, just like displaying AM/PM vs. military or decimal places.use a datepart() on that and you'll see there is still a date.- Jeff |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-19 : 10:44:18
|
| Jeff,Thanks...I know it's storing a date...The 0 date I alway thought was 1900-01-01But the VB guy is telling me that he only gets the time portion...when the date is 1899-12-30...Also, if you type in just a time in EM, it defaults to 1899-12-30...not the 0 date 1900-01-01Now the problem is, rockect scientists boy actually coded for this, and I'm not sure how far reaching it is...They need to change the dates (hours of operation...what an idiot...plus the time is also hard coded in the app)And since I made them move this "app" (for lack of a better term) in to a pure production environment, I have to write scripts that I can hand off to the prod dba...I'm thinking I'll be ok of I use that date...but sheesh, what a load of cr_pThis is the "app" they said has been running for years with out a single hitch...well yeah, if you have some mucking around every minute of every day...They're thinking that I may have been correct in my initial assesmnet and are now planning on a re-write...A wise consultant once said: "Chaos means money"But I'm an employee damn it...No OT...Brett8-) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-11-19 : 10:56:52
|
| Yeah, i get the same results, too. I think that in SQL Server, the "base date" is 1/1/1900 while in VB it's 12/31/1899. It seems like an issue with EM that it does dates the "VB" way when you enter them, and NOT the SQL Server way; it must do some conversion on its own before passing the value to SQL server as storing it. Very odd!In SQL, if you execute this:declare @d datetime;set @d = '10:10 AM'select @dIt clearly show 1/1/1900 as the datepart ... But, as you mention, in EM, if you open a table and enter:10:10 AMin a datetime field and then later query it, it shows up as 12/31/1899 ! Another reason NEVER to do data entry with EM I guess!Good luck !!- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-19 : 11:12:02
|
| But he built the app that way on purpose!AaaaaaaaaaahhhhhhhhhhhhhhhhhhhhhBrett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-21 : 11:33:27
|
| But the data being returned to the vb app is just the time component...And if it is the datetime...it blows the app...I hate this project...Brett8-) |
|
|
|
|
|
|
|
|
Magic Date?