Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
420 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 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

722 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