Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-04-21
4
Medium Priority
?
422 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 2000 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

824 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