none
insert only time in sql server database

    Question

  • hi,

    i want to insert only time in my sql server database.but when m try to insert time in database it takes time+default  date. i want to eliminate this default date.

    plz any one know this then tell me.

    Regards,

    shruti.

    Friday, September 08, 2006 6:18 AM

Answers

  • It is not possible to do this automagically using a native SQL Server date datatype.  The question I would have is are you trying to store a point in time?  Or a quantity of time?  And will you need to do any math?

    However, if you format this datetime value as:

    select convert(varchar(10),getdate(),108)

    You can get back that  format.  If you make sure that all of your dates are relative to some point in time (like 1/1/1900) then you are good to go.  The other possibilites are to use a integer and store the number of minutes from midnight, but that has the same issues that you have to format it. It will make doing intra day math easy too. 

    You also might just consider storing it as a varchar and store only '12:12:12 PM'.  Just make sure that the time can be converted to a datetime if you need to do date math (ignoring the date of course.)

    Friday, September 08, 2006 1:49 PM
    Moderator
  • Starting with SQL Server 2008 it is "dream come true", TIME only data type:

    DECLARE @TimeOnly TABLE (ID INT IDENTITY(1,1) PRIMARY KEY,
    DATEonly DATE default (CURRENT_TIMESTAMP),
    TIMEonly TIME default (CURRENT_TIMESTAMP),
    DATEandTIME datetime default (CURRENT_TIMESTAMP));
    
    
    INSERT @TimeOnly  DEFAULT VALUES;
    WAITFOR DELAY '00:00:01';
    
    INSERT @TimeOnly  DEFAULT VALUES;
    WAITFOR DELAY '00:00:01';
    
    INSERT @TimeOnly  DEFAULT VALUES;
    WAITFOR DELAY '00:00:01';
    
    INSERT @TimeOnly  DEFAULT VALUES;
    WAITFOR DELAY '00:00:01';
    
    INSERT @TimeOnly  DEFAULT VALUES;
    WAITFOR DELAY '00:00:01';
    
    
    SELECT * FROM @TimeOnly;
    
    /*
    ID	DATEonly	TIMEonly	DATEandTIME
    1	2012-09-30	09:08:44.3000000	2012-09-30 09:08:44.300
    2	2012-09-30	09:08:45.3130000	2012-09-30 09:08:45.313
    3	2012-09-30	09:08:46.3300000	2012-09-30 09:08:46.330
    4	2012-09-30	09:08:47.3400000	2012-09-30 09:08:47.340
    5	2012-09-30	09:08:48.3570000	2012-09-30 09:08:48.357
    */

    Blog post:

    http://www.sqlusa.com/bestpractices/timeonly/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Tuesday, December 13, 2011 12:21 PM
    Moderator

All replies

  • There are only two date data types available in SQL Server (DateTime and SmallDateTime). So if you use any of these two data types, it will use the default date along with your specified time. It is just like how it takes a default time when you only insert date.

    Here is a small workaround.

    Create Table Schedule( ScheduleID Integer Identity, ScheduledTime DateTime )

    Go

    Insert Into Schedule( ScheduledTime ) Values( '10:15:00 AM' )

    Go

    Select ScheduledTime As DBScheduledTime, Convert( VarChar( 10 ), ScheduledTime, 114 ) As ScheduledTime

    From Schedule

    Go

    Drop Table Schedule

    Go

     

    Hope this helps.

     

    Thanks

     

    Narayanan

     

    Friday, September 08, 2006 6:53 AM
  • 
    There is no way.  The two native temporal datatypes currently offered in versions of SQL Server are SMALLDATETIME and DATETIME -- both, you'll notice, have "date" as well as "time" in their names.  The two are inseperable.  What you should do is insert the time with the default date, and remove the date component for display when you need to show the time on a screen, etc.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     

    hi,

    i want to insert only time in my sql server database.but when m try to insert time in database it takes time+default date. i want to eliminate this default date.

    plz any one know this then tell me.

    Regards,

    shruti.

    Friday, September 08, 2006 6:55 AM
  • thanks for yr response but i want my sql server database save the data like

    12:12:12 PM..Database must contain only time value nt default date value.

    currently it saved data like 1/1/1900 12:12:12 PM and i want it save as 12:12:12 PM

    plz give me solution to do this.

     

    Regards,

    Shruti.

     

    Friday, September 08, 2006 8:03 AM
  • That is the closest representation of "Time" you will get...

    Datetime formats are contructed from 2 "parts" - An Integer representing the "days" after 1-1-1900  and a real part representing the "Time"

    So "translated" 12:00 PM would be 0,5 

    You can add this time to any "pure Date" to get a correct "Datetime" So to get the time you need to ignore the date part. If you want to output the time only, then you need to look at the convert functions and cut the parts away that will represent the date

     

    Hope that helps

     

    Friday, September 08, 2006 9:07 AM
  • hello,

    right now i am having "1/01/1900 12:12:12 PM" this format saved in my sql Server Database.but i want it to be saved as "12:12:12 PM"  in database.

    datatype of my table is datetime with length of 8.

    Shruti.

     

    Friday, September 08, 2006 9:42 AM
  • Sruthi,

    i think it is not possible to store only time into a column of datetime datatype.

    you can do it only when you make the datatype of the target column as varchar, and while inserting value into that column select only time part from date value by using cast and substring functions.

     

    Friday, September 08, 2006 1:03 PM
  • You cannot insert time only.
    I can suggest a some workrounds:

    1. If you have SqlServer2005 you can use a user defined type, so you can control almost any aspect of your type

    2. Store the number of seconds from the midnight, it far easy to have a function that decodes/encodes from/to seconds.

    3. As someone suggested you, store the datetime and use convert when select.

    I cannot see any other way
    Friday, September 08, 2006 1:26 PM
  • It is not possible to do this automagically using a native SQL Server date datatype.  The question I would have is are you trying to store a point in time?  Or a quantity of time?  And will you need to do any math?

    However, if you format this datetime value as:

    select convert(varchar(10),getdate(),108)

    You can get back that  format.  If you make sure that all of your dates are relative to some point in time (like 1/1/1900) then you are good to go.  The other possibilites are to use a integer and store the number of minutes from midnight, but that has the same issues that you have to format it. It will make doing intra day math easy too. 

    You also might just consider storing it as a varchar and store only '12:12:12 PM'.  Just make sure that the time can be converted to a datetime if you need to do date math (ignoring the date of course.)

    Friday, September 08, 2006 1:49 PM
    Moderator
  • If you keep the Datetime Format of 1.1.1900 12:12:12 you will be able to do math. with the field. You are able to add it to todays date and compare against "now()" for example. Just treat the 1.1.1900 as no date at all :)
    Friday, September 08, 2006 1:56 PM
  • 
    And I want a pony.  Looks like neither of us will get what we want today, so we both have to work within the bounds of reality.  Reality is, there is no way to do what you want.  But work with the tools you have, and you can achieve the same end result.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     

    thanks for yr response but i want my sql server database save the data like

    12:12:12 PM..Database must contain only time value nt default date value.

    currently it saved data like 1/1/1900 12:12:12 PM and i want it save as 12:12:12 PM

    plz give me solution to do this.

    Regards,

    Shruti.

    Friday, September 08, 2006 4:54 PM
  • insert row first without time, then update same row's time column with time will do the trick
    Saturday, October 07, 2006 1:30 PM
  • Can't be done? Huh. I was seaching for the answer to this today. Alright, I can deal with having the default date in there. But what really confuses me now is that all of the existing values in this column in this database are only the time value. Existing asp pages (which both create and update these records) seem to have no problem just putting the time in there. No acrobatics. Now, I'm using an UPDATE query, and they're using an ADODB.Recordset object. I guess that makes a difference?1
    Wednesday, October 18, 2006 7:05 PM
  • As discussed in this thread, there is nothing like a time or date literal specification in SQL Server. So if you specify only date for value (client or server-side) the time part will default to 12:00AM and if only time is specified the date part will default to 19000101. Please take a look at the Books Online topics on how datetime and smalldatetime literals can be specified. For now, you have the folowing alternatives:
     
    1. Use native smalldatetime & datetime and built-ins to manipulate the data. The disadvantage is that you incur more storage and you need to manipulate date/time part for presentation purposes
    2. Use your own storage format (int, CLR UDT, string etc). SQL Server doesn't know that the value is date time so indexing and ordering can be a problem depending on your format. Domain validations are hard because you can't use built-in data types in client or server (datetime/smalldatetime) to manipulate the values without conversion from one form to another. The advantage here is that the storage can be optimized. And display is a question of the chosen format.
     
    Wednesday, October 18, 2006 7:43 PM
  • If you use SQL Enterprise Manager and enter just a time in a datetime column, it does indeed just show the time when you open the table. If you select the data using SQL Query Analyzer, it show the date portion is December 31, 1899.

    This is really just a quirk in whatever application you're using to display the data.
    Thursday, April 12, 2007 9:09 PM
  • I know this post is old but I have to add my 2 cents. When you add just the time to a datetime it seems to append the date to the front of the datetime in the form of "12/30/1899". I saw this problem when converting code from VB to C# in that when VB would send Microsoft SQL Server a time object only it would be cast into the datetime and added to the row with the 12/30/1899 date attached. VB would also ignore this date when pulling the same data out and casting into a time object. C# would not allow this and the database API would insert 1/1/1900 if only the time was specified which was different and broke the VB code running from the same database. So before sending in the time from C# I added the default blank date of 12/30/1899 to the time parameter to trick the API into ignoring the date portion. That date is also int -2 so this works: convert(datetime, -2 + @time) and for some reason all API's concerned ignore the date. For now at least.

    Tuesday, December 11, 2007 9:39 PM
  • I had the same problem - heres what i did: Just make three columns Hour (int), Minute (int), and Meridien (varchar(50), AM/PM) (add second if need be) when pulling from the db just order by hour, minute, meridien.. or to REALLY simplify cut the meridien and use military time.

    Thursday, December 20, 2007 1:28 AM
  • hi,

    i want to insert only time in my sql server database.but when m try to insert time in database it takes time+default  date. i want to eliminate this default date.

    plz any one know this then tell me.

    Regards,

    shruti.

    This is functionally possible if you manually construct the query.

    string query = string.Format("INSERT INTO test (DateOnlyField, TimeOnlyField) VALUES ('{0}', '1899-12-30 {1}')", DateTime.Today.ToShortDateString(), TimeString)
    ...

    When you select from the table, the DateOnlyField should show only a date, and the TimeOnlyField should show only a time. The Date and Time are both stored in the field, but the result should only show one or the other.

    Kasey
    Sunday, May 10, 2009 1:04 AM
  • No, as already said, it is impossible to have only time or only date in a datetime column. Datetime has both parts. Period.

    You can decide to have a standard value for date or time and then some presentation layer can decide to not show that part if is has that standard value. But that is a very different thing from not having that part. Elaboration found here:
    http://www.karaszi.com/SQLServer/info_datetime.asp
    Tibor Karaszi
    Sunday, May 10, 2009 5:16 AM
    Moderator
  • In SQL Server 2008 use the TIME datatype.
    In SQL Server 2005 create a CLR user defined type.

    Paul
    Sunday, May 10, 2009 10:10 AM
  • Hi

        AM USING SQL SERVER 2000

        HOW TO INSERT ONLY TIME FIELD IN THE TABLE ?

    Saturday, May 08, 2010 7:22 AM
  • Please read the earlier posts in this thread. What you ask for is impossible. For instance, here is my earlier reply:

    No, as already said, it is impossible to have only time or only date in a datetime column. Datetime has both parts. Period.

    You can decide to have a standard value for date or time and then some presentation layer can decide to not show that part if is has that standard value. But that is a very different thing from not having that part. Elaboration found here:
    http://www.karaszi.com/SQLServer/info_datetime.asp


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Saturday, May 08, 2010 7:49 AM
    Moderator
  • I am having same problems, But the problem is user of data is 3rd party application, which does not under stand to drop the date part or manipulation of data from datatable.

    or does not convert strings to  time parts only. 

    Friday, May 14, 2010 1:45 PM
  • hi,

    i want to insert only time in my sql server database.but when m try to insert time in database it takes time+default  date. i want to eliminate this default date.

    plz any one know this then tell me.

    Regards,

    shruti.

    select

     

    convert(varchar,getdate(),114)



    Paresh Prajapati
    http://paresh-sqldba.blogspot.com/
    LinkedIn | Tweet Me | FaceBook | Brijj
    Friday, May 14, 2010 1:52 PM
  • Asim,

    here are three ways to get the time from a DATETIME field.

    DECLARE @t TABLE(LoadDate DATETIME)
    INSERT INTO @t
    VALUES (GETDATE())
    INSERT INTO @t
    VALUES ('10-12-2009 13:03:10')
    
    SELECT SUBSTRING(CONVERT(VARCHAR(24), LoadDate, 121), 12, 12) AS [Time Only],
    CONVERT(VARCHAR(8),LoadDate,108) AS HourMinuteSecond,
    SUBSTRING(CONVERT(VARCHAR(20), LoadDate, 9), 13, 5) 
    + ' ' + SUBSTRING(CONVERT(VARCHAR(30), LoadDate, 9), 25, 2) AS [AMPM Time]
    FROM @t
    

    Abdallah El-Chal, PMP, ITIL, MCTS
    Friday, May 14, 2010 2:16 PM
  • I had a same problem, i used this query which eliminates date part and gives you just time,

     

     

    (select (Cast(Replace(Convert(varchar(10), asdf, 108), ':', '') As int

    ))

     

    from TABLE NAME)

    ANK HIT
    • Proposed as answer by Waqar Khan S.E Tuesday, December 13, 2011 11:34 AM
    • Unproposed as answer by Waqar Khan S.E Tuesday, December 13, 2011 11:34 AM
    • Proposed as answer by ank hit Tuesday, June 12, 2012 4:03 PM
    Thursday, March 31, 2011 1:42 PM
  • If you are doing all this "Date & Time Picker" so the following simple code ll help you ..

    first of all you have to change the formate of "Date & Time Picker" to "time" from properties windows..so then you want to insert time in your database so follow this code..

     

        String strQuery = "INSERT INTO  table( time ) VALUES(' " + DateTimePicker.Value + " ')"; 

    .

    (and the rest of code for to execute this query ...)

    .

     

     

    Tuesday, December 13, 2011 11:41 AM
  • Starting with SQL Server 2008 it is "dream come true", TIME only data type:

    DECLARE @TimeOnly TABLE (ID INT IDENTITY(1,1) PRIMARY KEY,
    DATEonly DATE default (CURRENT_TIMESTAMP),
    TIMEonly TIME default (CURRENT_TIMESTAMP),
    DATEandTIME datetime default (CURRENT_TIMESTAMP));
    
    
    INSERT @TimeOnly  DEFAULT VALUES;
    WAITFOR DELAY '00:00:01';
    
    INSERT @TimeOnly  DEFAULT VALUES;
    WAITFOR DELAY '00:00:01';
    
    INSERT @TimeOnly  DEFAULT VALUES;
    WAITFOR DELAY '00:00:01';
    
    INSERT @TimeOnly  DEFAULT VALUES;
    WAITFOR DELAY '00:00:01';
    
    INSERT @TimeOnly  DEFAULT VALUES;
    WAITFOR DELAY '00:00:01';
    
    
    SELECT * FROM @TimeOnly;
    
    /*
    ID	DATEonly	TIMEonly	DATEandTIME
    1	2012-09-30	09:08:44.3000000	2012-09-30 09:08:44.300
    2	2012-09-30	09:08:45.3130000	2012-09-30 09:08:45.313
    3	2012-09-30	09:08:46.3300000	2012-09-30 09:08:46.330
    4	2012-09-30	09:08:47.3400000	2012-09-30 09:08:47.340
    5	2012-09-30	09:08:48.3570000	2012-09-30 09:08:48.357
    */

    Blog post:

    http://www.sqlusa.com/bestpractices/timeonly/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Tuesday, December 13, 2011 12:21 PM
    Moderator
  • It is not possible to insert standalone Time into SQL database but you can take only Time from user inputs like textbox and parse it to DateTime format caution but the provided input must be in proper format like HH:MM:SS, HH:MM note along with input time you will get current date automatically. Now you can insert provided time into database and retrive it for calculation
    using different date methods on it.

    code example

    string date = "12:15 PM";
    DateTime datevalue = DateTime.Parse(date);  
    Labelcheck.Text = datevalue.ToString();

    output

    10/4/2012 12:15:00 PM

     
    • Proposed as answer by dipen lama Thursday, October 04, 2012 8:57 AM
    Thursday, October 04, 2012 8:57 AM
  • >It is not possible to insert standalone Time into SQL database

    Yes it is possible. Starting with SQL Server 2008 TIME data type is available:

    DECLARE @Date DATE = CURRENT_TIMESTAMP;
    
    DECLARE @Time TIME =  CURRENT_TIMESTAMP;
    
    SELECT @Date, @Time;
    -- 2012-10-04	05:35:09.9730000


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Thursday, October 04, 2012 9:35 AM
    Moderator
  • It worked for me and I am using SQL server 2005
    Sunday, October 07, 2012 8:50 AM
  • There's no time datatype in SQL Server 2005. There is datetime and smalldatetime. Both of them has both dat and time. In 2005, there's no datatype for only time.

    Tibor Karaszi, SQL Server MVP | web | blog

    Sunday, October 07, 2012 11:52 AM
    Moderator
  • Yes that's true so i used above technique to take time input from textbox , where a user has to insert his start time and end time
    Monday, October 08, 2012 10:06 AM