Solved

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

Posted on 2004-04-21
4
419 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

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…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

630 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