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
Let us consider the following dataset
Create table testing(id int identity(1,1),name varchar(100));
insert into testing(name)
select ‘test1’ union all
select $identity from testing;
What is the result and what is $identity?
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
create table product
product_id int ,
insert into product (product_id , product_name)
select 1001,'Television' union all
select 1002,'Mobile' union all
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
select product_id, product_name from product where (product_name = @product_name or @product_name is null)
Now see the result for the following procedure calls
EXEC proc_list_product 'mobile'
The result is
The result is