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 |
|
borowik80
Starting Member
4 Posts |
 Posted - 2012-09-30 : 07:20:15
|
| Hi All,When I execute following statement:INSERT INTO [tb] (dt) values ('12/23/2000 12:00:00 AM');I get record without time part.If I change time to any other e.g. 12:01:00 I get record with date and time.What should I do to get correct record in mentioned case?Luk |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-30 : 18:11:38
|
What is the data type of the column dt? You can find out using:SELECT data_type FROM INFORMATION_SCHEMA.[COLUMNS]WHERE tablename = 'tb' AND column_name = 'dt' If the data type is a datetime type (datetime, datetime2, smalldatetime etc.), it stores the date and time part - the fact that you are not seeing time in the first case is just an artifact of the display. If you do want to display it in a specific format, you can convert it the desired string format - see here: http://msdn.microsoft.com/en-us/library/ms187928.aspxIf it is not a datetime type, can you post what the data type is? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-01 : 11:24:37
|
quote:
Originally posted by borowik80
Hi All,When I execute following statement:INSERT INTO [tb] (dt) values ('12/23/2000 12:00:00 AM');I get record without time part.If I change time to any other e.g. 12:01:00 I get record with date and time.What should I do to get correct record in mentioned case?Luk
12:00:00 AM is start of the day and represented as 00:00:00 in datetime field. If you want to store time as 12:00 AM you should be storing it as varchar (which is highly not recommended)date values are internally stored as numbers in SQL and you should not be worried on the format to store them. You can always retrieve and show them in way you want using rich set of date format functions available at your front end application or even using CONVERT() function in T-sql. Storing them as varchar has challenge of requiring lots of unwanted type conversions while doing date manipulations with the values and would affect query performances as well!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
borowik80
Starting Member
4 Posts |
Posted - 2012-10-03 : 07:57:56
|
| [/quote]12:00:00 AM is start of the day and represented as 00:00:00 in datetime field. If you want to store time as 12:00 AM you should be storing it as varchar (which is highly not recommended)date values are internally stored as numbers in SQL and you should not be worried on the format to store them. You can always retrieve and show them in way you want using rich set of date format functions available at your front end application or even using CONVERT() function in T-sql. Storing them as varchar has challenge of requiring lots of unwanted type conversions while doing date manipulations with the values and would affect query performances as well![/quote]Thanks for explanation! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 10:29:42
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Time value after insert is missing