Is DATE formatting in SQL good?

I have seen lot of people on the forums asking “How do I format my datetime values in a specific format?”

Well. While you can very well use CONVERT or FORMAT function, you need to think if it is really needed to do the formatting in sql itself.

Here is what happens when you do date formatting in sql

  • Formatted Dates become Varchars and wont allow date related calculations (dateadd, datediff,etc)
  • They wont allow to make use of index (if defined) if formatted at where clause
  • Web page, reports, etc treat them as varchars (calculations, Ordering, etc wont work properly)

So what is the proper way to do this? If you want to show them in a web page, reporting tool, etc let them do the date formatting . All of them support date formatting and it is really easy.

 

When should you do DATE formatting in sql?  You can do it for the following cases

  • Export results to text file (csv, etc) with specific date format
  • Import data from other sources where dates are in different formats
  • Front end application can’t be changed but it needs specific date formats for display  purpose

Let me know if your thoughts on this

Advertisements

Default values in stored procedure

Did you know that you can have default values for the parameters of the stored procedure? It is very similar to assigning default value to a column of a table.

Let us consider this dataset

use tempdb

create table product
(
product_id int ,
product_name varchar(100)
)


insert into product (product_id , product_name)
select 1001,'Television' union all
select 1002,'Mobile' union all
select 1003,'Vehicle'

Suppose you want to pass product name and get the details for that product or you do not want to pass any value and list out all details from the table. You can create a procedure with a parameter having default value of NULL as shown below

create procedure proc_list_product
(
@product_name varchar(100)=null
)
as
Begin
select product_id, product_name from product where (product_name = @product_name or @product_name is null)
End

Now see the result for the following procedure calls

EXEC proc_list_product 'mobile'

The result is

product_id   product_name
----------- ---------------------
1002         Mobile


EXEC proc_list_product

The result is

product_id   product_name
----------- ---------------------

1001         Television
1002         Mobile
1003         Vehicle