locked
Quick question about a data INSERT command. RRS feed

  • Question

  • Hello All!

    I am using SQL server and I need to insert some data about class courses.  There is a StartTime and an Endtime column.  I was trying to use the TIME data type in my CREATE statement, but kept gettin errors when inserting the data because occasionally some of the courses listed don't have a time, but instead are listed as "TBA".  I am thinking there is a conversion to use to allow for an actual time to be entered or for TBA  to be entered. Can anyone help with this?  Also if there is a conversion, where in the insert statement should I place it. (The time format is like this....09:00, 10:30, 11:45..etc)

    Thanks in advance!


    Sample: Data

    INSERT INTO CRSSECTION  (CsId,CourseId,Section,TermId,FacultyId,Day,StartTime,EndTime,RoomId,ActualCount,MaxCount)
    VALUES 

    (1101, 'CIS265', '01', 'WN03', 111, 'MW', '09:00', '10:30',13, 13, 30),

    (1210, 'CIS253', '31', 'SP03', 123, 'F', 'TBA', 'TBA', 19,1, 2);

    • Edited by Masty Wednesday, April 6, 2011 5:33 PM
    Wednesday, April 6, 2011 5:27 PM

Answers

  • If the Time is to be announced, it means it's not yet known. For unknown values we use NULL in the database. So, you can create a table with 2 TIME fields and make them both nullable.

    I suggest to keep time fields as TIME and not use VARCHAR. You may want to check this article

    Dear ISV: You’re Keeping Me Awake Nights with Your VARCHAR() Dates. if you're not sure which data type to chose.

    In the INSERT statement you can use NULLIF(TimeField, 'TBA') -- this will convert TBA to NULL.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog




    • Proposed as answer by Hasham NiazEditor Wednesday, April 6, 2011 5:34 PM
    • Edited by Naomi N Wednesday, April 6, 2011 5:35 PM
    • Marked as answer by Masty Thursday, April 7, 2011 11:46 AM
    Wednesday, April 6, 2011 5:31 PM
  • use tempdb 
    Go
    
    Declare @myTable Table (StartTime Time)
    
    Declare @myInput Varchar(5) 
    Set @myInput = '09:00'
    Insert Into @myTable 
    Select (Case @myInput When 'TBA' Then '00:00' Else @myInput End)
    
    Set @myInput = '11:00'
    Insert Into @myTable 
    Select (Case @myInput When 'TBA' Then '00:00' Else @myInput End)
    
    
    Set @myInput = 'TBA'
    Insert Into @myTable 
    Select (Case @myInput When 'TBA' Then '00:00' Else @myInput End)
    
    Select * From @myTable 
    

    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Hasham NiazEditor Wednesday, April 6, 2011 5:34 PM
    • Marked as answer by Masty Thursday, April 7, 2011 11:46 AM
    Wednesday, April 6, 2011 5:32 PM

All replies

  • H Masty !

    I would suggest you to use the VARCHAR field for your 'TIME' column as it allows you more flexibility. What this will do is, you can pass time as well as string in your string command with this. Please let me know in case of concerns.

    Thanks, Hasham

    Wednesday, April 6, 2011 5:30 PM
    Answerer
  • If the Time is to be announced, it means it's not yet known. For unknown values we use NULL in the database. So, you can create a table with 2 TIME fields and make them both nullable.

    I suggest to keep time fields as TIME and not use VARCHAR. You may want to check this article

    Dear ISV: You’re Keeping Me Awake Nights with Your VARCHAR() Dates. if you're not sure which data type to chose.

    In the INSERT statement you can use NULLIF(TimeField, 'TBA') -- this will convert TBA to NULL.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog




    • Proposed as answer by Hasham NiazEditor Wednesday, April 6, 2011 5:34 PM
    • Edited by Naomi N Wednesday, April 6, 2011 5:35 PM
    • Marked as answer by Masty Thursday, April 7, 2011 11:46 AM
    Wednesday, April 6, 2011 5:31 PM
  • use tempdb 
    Go
    
    Declare @myTable Table (StartTime Time)
    
    Declare @myInput Varchar(5) 
    Set @myInput = '09:00'
    Insert Into @myTable 
    Select (Case @myInput When 'TBA' Then '00:00' Else @myInput End)
    
    Set @myInput = '11:00'
    Insert Into @myTable 
    Select (Case @myInput When 'TBA' Then '00:00' Else @myInput End)
    
    
    Set @myInput = 'TBA'
    Insert Into @myTable 
    Select (Case @myInput When 'TBA' Then '00:00' Else @myInput End)
    
    Select * From @myTable 
    

    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Hasham NiazEditor Wednesday, April 6, 2011 5:34 PM
    • Marked as answer by Masty Thursday, April 7, 2011 11:46 AM
    Wednesday, April 6, 2011 5:32 PM
  • I agree with Naom , it better to use what he is suggested.
    Wednesday, April 6, 2011 5:34 PM
    Answerer