Solved

Wierd situatuin with MSDE, DateTime problem cannot both select by time and return time only

Posted on 2004-04-21
4
404 Views
Last Modified: 2011-09-20
First of all you should know i just migrated from Access to MSDE.

I used the upsizing wizard to get from Access to SQL, now my problem is the DateTime fields, in Access i had fields which only stored the time ea. "14:00:00", the upsizing wizard has transformed this into a Datetime fields and set it to short Time, this means that it at view time (Using access to view data) looks okay "14:00" but if i click it this value appears 30-12-1899 14:00:00, well i would care if it didnt reflect my availability to run queries and select data but it does, and in a wierd way. When 30-12-1899 is infront of the time i cannot do ALL of my 100 queries which uses the time in a where sentence ea. where lane_time>'13:00'. But and this is the wierd thing when i select the lane_time field and prints it in VB i get rs.Fields("lane_time") = "14:00".
All new value added like this
  rs.addnew
  rs!lane_time = "14:00"
  rs.update
would get 30-12-1899 in front which messes up select but gives okay returns

Okay well this means that i cant do selects on time in SQL, not true, if i every time i add a new record add 01-01-1900 in front of the time, i can actually do the selects with time in where. But the rs.Fields("lane_time") returns = 01-01-1900 14:00:00 which is wierd, because either the value is 14:00 in both selects and returns or its not.

Is it true that SQL server is completely wierd here ? is there no way to get both select and returns working ???
0
Comment
Question by:mSchmidt
  • 2
4 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 10886010
try
select convert(varchar(5), lane_time, 108)
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10886029
>>Is it true that SQL server is completely wierd here <<
I don't think so
Datetime format is date AND time
If you want to stick to a given format in you select statements, and to store a time (date = 1900.01.01 = 0), you'll need to use CONVERT in your select statements

FYI, Yukon (new SQL Server version planned 2005) will provide separate datatypes for date, time, datetime, ...

Hilaire

0
 

Author Comment

by:mSchmidt
ID: 10886170
Hilaire -> i am trying not to have to change to much code, if i want to extract the daté and then do stuff i can also do TimeValue() in VB.. i would like to not have to do this.. Does any of you know why ADODB.recordset then doesnt return 1900.01.01 as 0 but instead 1899.30.12 ???
0
 
LVL 3

Accepted Solution

by:
Younkman earned 500 total points
ID: 10889090
Here is what T_SQL help said about time values:

Remarks
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.


-Y
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now