Time gets removed from date

Question

Time gets removed from date

thekingironfist

Valued Member

Points: 68
More actions
February 11, 2008 at 9:20 am

#179054

Hi folks, every time a date is entered into SQL Server 2000 as a 12:00:00 AM time, it becomes automatically truncated to only the date.
Example:
I type:
2/11/2008 12:00:00 AM
When I remove focus, it becomes:
2/11/2008
Any reason for this, or anyway around it? I would really like to have the time there.
Thanks,
James

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

What are you using to access the data? Considering that datetime fields store both the time and the date, what you're getting at seems to be a presentation issue (which is a function of the UI).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lowell SSC Guru Points: 323518 More actions · Answer 2

thekingironfist (2/11/2008)
Hi folks, every time a date is entered into SQL Server 2000 as a 12:00:00 AM time, it becomes automatically truncated to only the date.
Example:
I type:
2/11/2008 12:00:00 AM
When I remove focus, it becomes:
2/11/2008
Any reason for this, or anyway around it? I would really like to have the time there.
Thanks,
James

Remove focus? Where? In what application? I'm thinking you must be using an application that displays the date. I would guess the application is also inserting/updating the date...

sqlServer Datetime fields contain bot the date and the time....12:00:00 AM

means that the right side of the decimal is all zeros.....

for example in vb, Date() returns just the date portion with no time, but Now() ruturns the date and the time.

I think you have to review the code your applicaiton is using to insert the date/time.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

thekingironfist Valued Member Points: 68 More actions · Answer 3

Thanks for the replies! This was noticed in a standard ASP page that at 12:00 AM times, there was no time displayed, only dates. So I looked into the database and, sure enough, none of the datetimes had times in them if there was a 12 AM time in it. I've attached a series of screenshots showing (in Enterprise Manager) a change from 12PM to 12AM - notice how the time disappears when the changes get commited (my confusing "change focus" comment from earlier).

Ideas?

Thanks,

James

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

What is the data type of the column you are storing this in? If you're storing this in a datetime field, then you can simply use a FORMAT on the ASP page to make sure you always get date and time.

If you're not, then there's you issue. You really shouldn't store dates as strings, since you open the door to all sorts of validation issues (like invalid values, etc...)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

thekingironfist Valued Member Points: 68 More actions · Answer 5

Nope, the data is saved as datetime (see attached picture). I just can't figure out why it is auto-truncating the datetime... (see pics from previous post).

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Then set up the FORMAT function in the ASP page to control.

There's no truncation going on. The time component is stored as the fraction of a day, so 12:00:00 AM means 0 after the decimal point, so it may or may not display. Until you TELL it what display format you want, it may decide to use any format it pleases, which I think is what you at running into here.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

thekingironfist Valued Member Points: 68 More actions · Answer 7

Gotcha, I was thinking that but I just couldn't tell. I've inherited an old ASP app that uses VBScript for its "codebehind" which doesn't have Format (only FormatDateTime I believe, which is not so nice), so I won't hassle much about it then as eventually it will be phased out anyways. I will probably just do a check now and replace as necessary.

Thanks,

James

Adiga One Orange Chip Points: 27284 More actions · Answer 8

Use the Convert function while inserting date and time. This will hep.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga