how to insert "time only" into a datetime field
-
Hi I was wondering if anyone can help me out. I want to insert/update a time into a datetime field. Example insert/update 11:00 AM but my result right now is 1/1/1900 11:00:00 AM. I'm trying to use convert(varchar, 11:00:00, 114) but thats not working. It's something simple that I'm not understanding... I have to learn how to save all my scripts because I had figured this out before!
-
There's no time datatype on sql server. I would strongly suggest you keep both the data and time 99% of the time depending on the requirements.
Also the fact is that the time is saved on the server and it's then just a matter of presenting the data to users, so that becomes easy then. Just leave it like that!
-
If you only want time then store the 11:00.
It will store 1900/01/01 11:00 (or something similar). But always remember when using that field, the data is mis-leading unless converted on the extraction.
select hour(mytime) + ':' + minute(mytime)
from yada
-
Yeah I figured it out...
CONVERT(DATETIME, '1899-12-30 11:00:00', 102)
will give me my desired result
thanks
-
I recommend to use varchar(8) or char(5) to hold just time, because storing it along with some dummy date would be really misleading.
-
How do you perform the date operation without reconverting back (which renders any indexing useless)?
-
I had the same problem and I tried every possible way I could think of.
Thanx for the solution John.
-
You are welcome, I spent about a day trying to figure that one out until I tried the QBE in Enterprise Manager... sometimes it's good to let sql2k do the work for you HA!
..... -
No need because the column is intended to just store the time portion... The date is stored in a different column that is indexed..
Granted I could of have stored the date and time in one column (9/27/2006, 11:00:00AM) instead of two and then by code split the date and time in the application but I would have to do that for about 100 tables, plus the DBA probably would look at me with the seriousness of your f'ing nuts, because thats not going to happen.
-
all intense purposes... the task was to move away from declaring the column a char or varchar so we wouldn't have to validate the time in the code of the application. Also, it's not really misleading when viewing the data, in the column the time portion is only showed in the DB without the dummy dates. If I just do an insert/update without converting the time, say 9:00 pm... in the db, the column will put a dummy date with the time, which is not the desired result.
-
Sure... until you have to do a calculation with both... then, as Remi suggested, it won't be possible to use an index even if both columns are indexed.
Just as a refresher (maybe a primer for some
), date/times are actually stored as floating point numbers where the numbers to the left of the decimal point represent whole days and numbers to the right of the decimal represent a fraction of a day... in other words... TIME.Storing a time with no date will always return 1900-01-01 hh:mm:ss.mil... the 1900-01-01 portion shows up when you do a select because SQL Server date/time is based on the number of days since midnight on 1900-01-01... it is not "some arbitrary" number as some have suggested. For proof, try SELECT CAST(0 AS DATETIME) and see what you get.
Storing time as a char is a huge mistake... what if you need to add the times up? What if you need to add 10% to all times to create an estimate? What if you want to change the format from an AM/PM format to a 24 hour format but just for one or two of your reports? And, then have the boss change his/her mind and have you put it back again. What if you want to know the number of hours and minutes between two dates and their associated times? Oh sure... you can do your character based work arounds or convert to a datetime data type, but isn't that where you really need to start? And, most char based ops take longer than mathematical ops... date/times are nothing but numbers and the appropriate date/time functions just fly compared to char functions.
I'll say it again, if you store dates and times as char, you are really limiting your options and and performance.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That's terribly WRONG!!!
What you see in EM is not what you store in DB!!!
EM shows you not actual date value but result of conversion to varchar with VB or C deault "zero" date applied.
And this "zero" date is different in EM and in SQL Server. For EM it's '1899-12-30', for SQL it's '1900-01-01'
If you don't want to get into HUGE trouble you better close EM and don't open it again until you understand how SQL Server actually works.
_____________
Code for TallyGenerator
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply