Smalldate time Field problem

Last Post 30 Jun 2006 08:25 PM by quanta_1. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
quanta_1
New Member
New Member

--
29 Jun 2006 11:11 PM
Hi,

I have a smalldatetime field in a table which holds time in 12 hour clock.
Problem is that, when i insert time '12:00:00 AM' with date in a field, sql server clears my
time entry. But, when i enter '12:00:01 AM' it accepts.

Please help...


quanta_1
New Member
New Member

--
30 Jun 2006 04:25 AM
means,When i manualy enter time in table field by SQL Server Enterprise Manager 2000.

For eg,

Before
-------
24/06/2006 8:45:00 PM

After change time to 12:00:00 AM it changes to
--------------------------------------------------------
24/06/2006
nosepicker
New Member
New Member

--
30 Jun 2006 06:53 AM
Smalldatetime values are rounded to the nearest minute. If you are looking at the data in Enterprise Manager, it will choose to not display the time element if it's 12:00:00AM. However, the time element is actually there (you can confirm this by selecting the data in Query Analyzer). Remember that Enterprise Manager is just a GUI tool - use Query Analyzer for a more accurate representation of the data.
quanta_1
New Member
New Member

--
30 Jun 2006 07:06 AM
Ok thanks, but when i retrive the filed value in my VB6 form, it shows nothing there except date.
How can i do this?
quanta_1
New Member
New Member

--
30 Jun 2006 08:25 PM
Yes, i checked it in Query Analyzer where it shows me '2006-06-22 00:00:00.000'.
If i manually add one second in this value, i'll get '2006-06-22 12:00:01 AM' in my form.
quanta_1
New Member
New Member

--
01 Jul 2006 12:45 PM
I used the convert function, but still no success.
SQLUSA
New Member
New Member

--
26 Aug 2006 12:30 PM
>
means,When i manualy enter time in table field by SQL Server Enterprise Manager 2000.

For eg,

Before
-------
24/06/2006 8:45:00 PM

After change time to 12:00:00 AM it changes to
--------------------------------------------------------
24/06/2006
>

This is correct. You have to change the DATE if you want a different day!

Anywhere you change the time on 24/06/2006 you are still on the same day!!!
12:00:00 AM means midnight (the morning just starting) 00:00:00 military time.


Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/order2005/
The Best SQL Server 2005 Training in the World


Acceptable Use Policy
---