Insert Time in SQL (ONLY TIME) 1

[suoirotciv]

I have this table EmpLogs with a field tTimeIn, tTimeOut

Then I insert a record

db.Execute "INSERT INTO EmpLogs (tTimeIn, tTimeOut) VALUES ('7:00:00 AM','5:00:00 PM')

but when i view my table, the values are as follows:

iTimeIn = 1/1/1900 7:00:00 AM
iTimeOut = 1/1/1900 5:00:00 PM

but when i test MANUALLY TYPE to insert a record and add the following values iTimeIn = 7:00:00 AM iTimeOut = 5:00:00 PM

it was displayed as is.
========================================================

IS IT POSSIBLE THAT I CAN INSERT A RECORD WITHOUT THAT 1/1/1900?

Please pardon the grammar.
Not good in english.

 

[swampBoogie]

No, not if you are using a datetime column. SQL server does not have a time datatype.

You can always remove the datepart when displaying data by uisng the convert function.

 

[rostler]

Hi, If no date is supplied the default of january 1, 1900 is assumed. As you are only entering the time part into a datetime filed that is what is happening. Try the following to get only the time part out

convert(varchar,iTimeOut, 108)

Richard

 

[suoirotciv]

swampBoogie >> yah . . i'm using a datetime as datatype

but why is it that i when just simply type the values as 7:00:00 AM it was accepted as that . . . without the 1/1/1900?

rostler >> thanks for the info

Please pardon the grammar.
Not good in english.

 

[rostler]

You are using a datetime field and this must have a date part and a time part. Because you did not enter a date, only a time, SQL Server filled in the date part for you using the default date value of 1/1/1900.

Richard

 

[SQLBill]

Look at it this way....datetime data type has a built-in trigger. If you don't input a date, it adds the default date of 01/01/1900. If you don't input a time, it adds the default time of 00:00:00.000.

There's no way to get around it if you are using datetime data type.

-SQLBill

 

[suoirotciv]

thanks to all of you for ur information . . .


but just wondering why is it that they just create a date datatype and another time datatype . . .




Please pardon the grammar.
Not good in english.

 

[wyldsider]

and why don't they create a data type for left handed programmers who want to create integers that are 48 bits?

when designing database types, they powers that be focused on the group of types that would provide the greatest coverage for the data that was to be stored there. in terms of complexity, it is fairly easy to craft some sql that will take the results of a datetime field and only return the time part, so datetime is acceptible for what you want to store.

now. my sql is a bit rusty, but I do remember seeing something posted here a while ago that took the results of such an operation and returned only the date part, so I assume the time part is equally trivial.


Jack De Winter
Software Developer
MedTel Software

http://www.medtel.on.ca

 

[SQLSister]

I have to disagree with you Jack. This is a reasonable set of data types to want and your tone was insulting. Yes the code is fairly trivial but converting every time you use something does add unecessary processing to your server. Apparently Microsoft also agrees that they need separate date only and time only data types because it is my understanding that the next version of SQL Server will have them.

 

[wyldsider]

My apologies, didn't mean to be insulting, just to illustrate a point.

However, if MS is only adding them now, they obviously didn't think they were that important up until this point. Yes, converting is a pain, but so is supporting another type of data in the server.

Jack De Winter
Software Developer
MedTel Software

http://www.medtel.on.ca

 

[SQLSister]

There is a big difference between "a pain" and inefficient. Perhaps the inefficienies weren't big enough to make their customer base complain about it until they were trying to serve up web pages to thousands of users simlutaneously or trying to pull records from tables with millions of records. When you do that, then every little tiny bit of performance you can improve becomes important. Based on this forum, I can tell you a significant number of people want to manipulate dates or times separately and these data types are long overdue.

 

[jimmYLepp]

Not to be insulting, but couldn't you use a char or a varchar datatypes if you would like to hold just the time values? this way you could store it any way you prefer and still be able to concatinate it with the current date.

Now, from my experiances in manufacturing and dealing with building daily plant schedules based off of just the time, that a char works great. Otherwise, I can't really think why someone would want just the time and not the date. So, I would agree with Jack an say that it isn't that important for MS to create a time datatype. I would like to see a mmddyyyy datatype, that would save some room.

jimmY

 

[suoirotciv]

SQLSister: Apparently Microsoft also agrees that they need separate date only and time only data types because it is my understanding that the next version of SQL Server will have them <<< I hope this will be sooner . . .

I'll never thought that this was a big issue to others . . . I thought it was just on me . . .

Please pardon the grammar.
Not good in english.

 

[rac2]

Think about Access and Excel which appear to have Date datatypes and Time datatypes. These are automatically displayed with the date-only or time-only parts. Seems like a handy thing, people don't have to be using the conversion functions. But it leads to confusion because these are really date serial numbers with fractions representing hours during a date. There are many posts related to this in the Access forum where the problem typically arises in a conditional expression.

Yet I wonder about a TimeOfDay data type. It seems you always want to be calculating elapsed time from a beginning and ending time. How can that be done in general since TimeOfDay starts over at zero everyday?

If you know that you will never be using a TimeOfDay to calculate elapsed time, then Varchar ought to work just fine. Where is the need or the value for a TimeOfDay datatype?

 

[JFoushee]

For what it's worth, I stumbled into this forum looking for the same answer.

When I inserted just time (like you did) in EM, then using Query Analyzer, I found that QA said the day was &quot;1899-12-30&quot;, two days earlier than your 1/1/1900.

When I did an INSERT in TSQL of &quot;1899-12-30 12:00am&quot;, it reproduced the similar scenario of just inserting time through EM.

Hope this helps.

 

[kaymc]

Yessir, 1899-12-30 seems to be the magical number.

Our system inserts a call duration value (in hours:minutes) into a datetime field. When you look at this field through EM you get 00:01:32 and the like, without any date component.

Recently I've been trying to insert some data from our old system and it's been bugging me that all of *my* time values were displaying with a 1-1-1900 prefix.

However, when you put stick an 1899-12-30 date on the front, EM drops the date component of the time stamp when displaying the value (in exactly the same way, one supposes, that it drops the 00:00:00 when displaying a midnight date).

So, as far as enterprise manager is concerned anyway, 1899-12-30 seems to be the date equivilant of the time 00:00:00.

 

[rac2]

Kuodos to JFoushee for sharing his discovery about SQL Server time. This is not mentioned in Books Online topics for Date and Time, DATETIME, or SMALLDATETIME.

Furthermore, 1/1/1900 is the year that will be used to store values which are inserted with only the time with both DATETIME and SMALLDATETIME. The interesting thing is that the beginning of time for DATETIME is 1/1/1753, not 1/1/1900. So 12/30/1899 is a valid date for a DATETIME type.

Oh the confusion; my datebook showed lunch with my best friend scheduled at 12:30:00 PM, but it was supposed to be in 1899! Blame it on the computer.

 

[clarkin]

12/30/1899 as a datetime will be stored as a -1.0 (internally) .. which works out as 1 day before 1 Jan 1900

Its beyond time SQL Server had separate Time & Date only data types. Lots of people have to use workarounds such as keeping a separate Hour and Minute int fields for statistical purposes.. not very efficient - but better than manipulating thousands of datetimes

So, in a few years we'll have em ..

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].