SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Time value after insert is missing		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

borowik80
Starting Member

4 Posts
Posted - 09/30/2012 :  07:20:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 09/30/2012 :  18:11:38  Show Profile  Reply with Quote
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.aspx

If it is not a datetime type, can you post what the data type is?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48097 Posts
Posted - 10/01/2012 :  11:24:37  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
Go to Top of Page

borowik80
Starting Member

4 Posts
Posted - 10/03/2012 :  07:57:56  Show Profile  Reply with Quote

[/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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48097 Posts
Posted - 10/03/2012 :  10:29:42  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000