12:00 AM smalldatetime field cuts off time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

12:00 AM smalldatetime field cuts off time

Author

Topic

djchrome
Starting Member

19 Posts

Posted - 2003-09-02 : 14:05:00

I've got a database field with a DataType of smalldatetime and when an ASP page tried to populate a record with a value of say 09/02/03 12:00:00 AM it inserts the record but without the time. I tried to manually type the time into the cell in the database but it still truncates it to show just the date. All other times are fine 12:01:00 AM, 12:00:00 PM etc...

Any easy fix for this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-02 : 14:12:32

Try it in Query Analyzer:


CREATE TABLE SomeTable
(
DateTimeColumn SMALLDATETIME NOT NULL
)

INSERT INTO SomeTable (DateTimeColumn)
SELECT '09/02/03 12:00:00 AM'

SELECT * FROM SomeTable

DROP TABLE SomeTable

You will get 2003-09-02 00:00:00 back as the result set. If you view the data in Enterprise Manager, you will get 9/2/2003 back. So you can run the SQL like this to get what you want:

SELECT CONVERT(VARCHAR(50), DateTimeColumn)
FROM SomeTable

Enterprise Manager and your ASP page are chopping off 00:00:00, which is the same as 12:00:00 AM. So you will need to use the CONVERT function to get what you want. For a specific format, use a style in the CONVERT function. See SQL Server Books Online for details.

Tara

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-03 : 03:02:09

And don't use Enterprise Manager to edit data. The more you use it, the more quirks like this you will find. Use Query Analyser for all SELECT, INSERT, UPDATE, etc statements.

Owais

Make it idiot proof and someone will make a better idiot

djchrome
Starting Member

19 Posts

Posted - 2003-09-03 : 12:40:28

Yeah I typically don't use EntMgr for data entry, but this one was weird so I wanted to input direct to see what I could find.

Thanks to mohdowais tip I just ended up fixing the problem in the ASP page by writing:

Date: <%=FormatDateTime(ItemStartDateTime,1)%>
Time: <%=FormatDateTime(ItemStartDateTime,3)%>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources