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.
| Author |
Topic |
|
phrankbooth
Posting Yak Master
162 Posts |
 Posted - 2007-03-01 : 11:46:11
|
| In the code below why does it not enter 10:00 AM but instead it enters: 1900-01-01 10:00:00.000 ?Create Table MyDateTest( DateColumn datetime)insert into MyDateTest values ('10:00 AM')select DateColumn from MyDateTest--PhB |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-03-01 : 11:51:37
|
| DateTime datatypes must always store both -- date and time. How things are formatted is one thing, but what is stored is always the two values.the expression '10:00 AM' when implicitly converted to a datetime has a value of '1900-01-01 10:00:00.000', since the date of 1/1/1900 is considered the "base date" in SQL Server, with a value of 0. So, to store just the time, that is how you do it, but sql must always put a date in there too.When you do math with two times at 1/1/1900, they add up perfectly since the date has a value of 0. When you output a datetime at 1/1/1900, you always have the ability, at your presentation layer, to format a datetime any way that you want, so you can very easily just ignore the date portion and output a format of "hh:mm" or whatever you want to only show the time. The same applies to dates without times -- even though you might say insert '1/1/2006' into the database, it is always inserted with a time as well, in this case the "base time" is 12:00:00 AM.read up on dateTime datatypes in Books On Line, do some googling for more info, and really be sure to understand what is happening behind the scenes when you run an INSERT statement like the one you have shown, it is very a important concept to grasp. Always remember that how things are formatted or converted to and from text is very different from the actual VALUEs that are stored in the database, and you should always concern yourself with the values of the data in T-SQL and don't worry about the formatting at the database layer.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
phrankbooth
Posting Yak Master
162 Posts |
Posted - 2007-03-01 : 11:58:13
|
| Wow, that was quick, yes OK, I think I got you now.Thanks for the quick turn around!--PhB |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
|
|
Inserting just the Time Part of the date?