Solved

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

Posted on 2004-04-21
4
407 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 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

14 Experts available now in Live!

Get 1:1 Help Now