How to store ONLY Time in SQL SERVER?

Only Time in SQL SERVER

Many a time we just need to store time part of date only, but when we look after SQL Server there is no such datatype which allow us to store only time part of the datetime.

So the question of the matter is how to store only time in sql table object. Here is a small exmple of storing time efficiently, is to use an integer column.

create table demoToStoreOnlyTime_tab(

myTotalHour smallint)

Command(s) completed successfully.

Now while inserting, you multiply the number of hours by 100, and add the minutes

insert into demoToStoreOnlyTime_tab  values( 100*datepart(hour,getdate()) + datepart(minute,getdate()))

(1 row(s) affected)

Rest let the developer do there work for formating while displaying time on front end

select CAST(LEFT(myTotalHour, LEN(myTotalHour)-2) + ':'+ RIGHT(myTotalHour, 2) AS VARCHAR(5)) as 'MyHour'  from demoToStoreOnlyTime_tab

MyHour

------

11:16

(1 row(s) affected)

Happy Coding :)

Post Reference: Vikram Aristocratic Elfin Share

Function that will identify why your code fails. (USING TRY CATCH)

Function in TRY CATCH

Always try to put your code in the TRY block and you check for the errors in the CATCH block. SQL Server has a bunch of functions that will help you identify why your code failed, here is a list of the functions and what they return

ERROR_NUMBER()
returns the number of the error

ERROR_SEVERITY()
returns the severity of the error

ERROR_STATE()
returns the error state number

ERROR_PROCEDURE()
returns the name of the stored procedure or trigger where the error occurred, this will be NULL if you run an ad-hoc SQL statement

ERROR_LINE()
returns the line number inside the routine that caused the error

ERROR_MESSAGE()
returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times

Let's run an example that generates a divide by zero error, in the catch we are just doing a simple select that calls the functions mentioned before to see what they return

begin try

      select 1/0 as 'DivisionResult'

end try

begin catch

      select

            error_number() as 'ErrorNumber',

            error_severity() as 'ErrorSeverity',

            error_state() as 'ErrorState',

            error_procedure() as 'ErrorProcedure',

            error_message() as 'ErrorMessage'

end catch

DivisionResult

--------------

(0 row(s) affected)

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure  ErrorMessage

----------- ------------- ----------- --------------- -------------------------------

8134        16            1           NULL            Divide by zero error encountered.

(1 row(s) affected)

As you can see we got all that information back, that was pretty nice. Let's take it to the next step

Create the following table to store all the error information in

create table LogErrors (ErrorTime datetime,

                  ErrorNumber int,

                  ErrorSeverity int,

                  ErrorState int,

                  ErrorProc varchar(max),

                  ErrorLine int,

                  ErrorMessage varchar(max))

Command(s) completed successfully.

Create this stored procedure that will insert into the table we just created

create procedure proc_InsertError

AS

begin

      insert LogErrors

      SELECT GETDATE(),ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE()

End

Command(s) completed successfully.

Run these 3 queries, they will generate 3 inserts into the LogErrors table

begin try

      SELECT 1/0

end try

begin catch

    EXEC proc_InsertError

end catch;

       

begin try

    SELECT convert(int,'a')

end try

begin catch

    EXEC proc_InsertError

end catch;

       

begin try

    SELECT convert(tinyint,300)

end try

begin catch

      EXEC proc_InsertError

end catch;

If you check now what is in the table, you will see 3 rows

select * from LogErrors

ErrorTime  ErrorNumber  ErrorSeverity ErrorState  ErrorProc   ErrorLine ErrorMessage

----------- ----------- ------------- ----------- ----------  --------  -----------

2012-01-09  8134        16            1           NULL         2        Divide by zero error encountered.

2012-01-09  245         16            1           NULL         9        Conversion failed when converting the varchar value 'a' to data type int.

2012-01-09  220         16            2           NULL         16       Arithmetic overflow error for data type tinyint, value = 300.

(3 row(s) affected)

Post Reference: Vikram Aristocratic Elfin Share

How could you ensure that your VIEW is binded to your table in such a way that the tables cannot be deleted without deleting the VIEW. (SCHEMABINDING)

Finally I got some time to write this post; yes it is quite possible with the schemabing clause, creating a view with SCHEMABINDING option locks the underlying tables and prevents any changes that may change the table schema.

Imagine that you have created a view and you have altered the schema of underlying table (deleted one column).Next time when you run your view, it will fail. Here is when SCHEMABINDING comes into picture

lets do some practical on this.. we having a able named  DEMOFORSCHEMABINDING_TAB.

select * from demoforschemabinding_tab

id          name                 department           salary         pf_contribution

----------- -------------------- ----------------- -------------- -----------------

1           Aadita               IT                   35000.00       12000.00

2           Aakarsh              Sofware              45000.00       13000.00

3           Aaliya               Insurance            56000.00       13500.00

4           Falak                Account              35000.00       12000.00

(4 row(s) affected)

Now we are trying to create a view without SCHEMABINDING option

create view vw_without_schmeabinding_demoforschemabinding_tab

as (select * from demoforschemabinding_tab)

Command(s) completed successfully.

select * from vw_without_schmeabinding_demoforschemabinding_tab

id          name          department  salary   pf_contribution

----------- --------      ----------  ------   ---------------

1           Aadita         IT         35000.00  12000.00

2           Aakarsh        Sofware    45000.00  13000.00

3           Aaliya         Insurance  56000.00  13500.00

4           Falak          Account    35000.00  12000.00

(4 row(s) affected)

Now let’s try to drop a column say pf_contribution

alter table demoforschemabinding_tab

drop column pf_contribution

Command(s) completed successfully.

Let’s see the table data.

select * from demoforschemabinding_tab

id          name                 department           salary

----------- -------------------- -------------------- --------

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00

(4 row(s) affected)

Yes the column has been dropped. Now it is having only four columns.

Let find the same record from the view we have created in the above step.

select * from vw_without_schmeabinding_demoforschemabinding_tab

Msg 4502, Level 16, State 1, Line 1

View or function 'vw_without_schmeabinding_demoforschemabinding_tab' has more column names specified than columns defined.

The reason for this error message: The underlying schema (structure) of table has been changed on which the view was created.

So the question of the discussion is how to ensure that the schema (structure) of the table cannot be modified or dropped unless that view is dropped or changed. Otherwise, the Database Engine raises an error when executing ALTER statement on the table.

The answer to this question is using SCHEMABINDING. Let’s take an example to explain SCHEMABINDING.

select * from demoforschemabinding_tab

id          name                 department           salary

----------- -------------------- -------------------- -----------

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00

(4 row(s) affected)

Now we will try to create a view on this table with SCHEMABINDING

create view vw_with_schmeabinding_demoforschemabinding

with schemabinding

as ( select id,name,department,salary from dbo.demoforschemabinding_tab)

Command(s) completed successfully.

Let’s run a select statement with the above created view

select * from vw_with_schmeabinding_demoforschemabinding

id          name                 department           salary

----------- -------------------- -------------------- ---------------------------------------

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00

(4 row(s) affected)

Now let’s try to modify the structure of the underlying table DEMOFORSCHEMABINDING_TAB

alter table demoforschemabinding_tab

drop column salary

Msg 5074, Level 16, State 1, Line 1

The object 'vw_with_schmeabinding_demoforschemabinding' is dependent on column 'salary'.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE DROP COLUMN salary failed because one or more objects access this column.

The ALTER fails and gives the message 'vw_with_schmeabinding_demoforschemabinding' is depends on column salary so it cannot be modified or dropped. So this is the benefit of schema binding in view that is it will not allow to modify the underlying structure of table if any view is depends on the table schema.

Post Reference: Vikram Aristocratic Elfin Share