SQL Calculate Days in Month

There is no option available in SQL to calculate days of a specific month, so it is very poor approach to write CASE statement in every single T-SQL.

USER DEFINED FUNCTION is the right way of doing these kind of tasks which have to be done on regular basis.

CALL:
select dbo.GetDaysinmonth(5)

OUTPUT:
31

T-SQL for Calculate Days in Month Function

USE [TESTDB]
GO
/****** Object:  UserDefinedFunction [dbo].[GetDaysInMonth]    Date: 01/31/2012 16:04:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetDaysInMonth] ( @pDate    DATETIME )
RETURNS INT
AS
BEGIN

    RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
                WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
                ELSE CASE WHEN (YEAR(@pDate) % 4    = 0 AND
                                YEAR(@pDate) % 100 != 0) OR
                               (YEAR(@pDate) % 400  = 0)
                          THEN 29
                          ELSE 28
                     END
           END

END


Enjoy!!

Read more »

SQL PROPER CASE Function

Guys many times we need to use proper casing for our website visitors and we need some method which can do proper casing for us. In SQL there is no System Function for this job. So we have to make our own function to do Proper Case for us.

Below is the syntax for "PROPER CASE" function and before that is the method to call SQL User Defined Functions

How to call:

SELECT dbo.[PROPERCASE]('This is test to check Proper case function')

OUTPUT:
This Is Test To Check Proper Case Function


T-SQL for PROPER CASE function



USE [TESTDB]
GO
/****** Object:  UserDefinedFunction [dbo].[PROPERCASE]    Script Date: 01/31/2012 15:30:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[PROPERCASE]
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
    IF @input IS NULL
    BEGIN
        --Just return NULL if input string is NULL
        RETURN NULL
    END
   
    --Character variable declarations
    DECLARE @output varchar(8000)
    --Integer variable declarations
    DECLARE @ctr int, @len int, @found_at int
    --Constant declarations
    DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int
   
    --Variable/Constant initializations
    SET @ctr = 1
    SET @len = LEN(@input)
    SET @output = ''
    SET @LOWER_CASE_a = 97
    SET @LOWER_CASE_z = 122
    SET @Delimiter = ' ,-'
    SET @UPPER_CASE_A = 65
    SET @UPPER_CASE_Z = 90
   
    WHILE @ctr <= @len
    BEGIN
        --This loop will take care of reccuring white spaces
        WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
        BEGIN
            SET @output = @output + SUBSTRING(@input,@ctr,1)
            SET @ctr = @ctr + 1
        END

        IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
        BEGIN
            --Converting the first character to upper case
            SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
        END
        ELSE
        BEGIN
            SET @output = @output + SUBSTRING(@input,@ctr,1)
        END
       
        SET @ctr = @ctr + 1

        WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
        BEGIN
            IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
            BEGIN
                SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
            END
            ELSE
            BEGIN
                SET @output = @output + SUBSTRING(@input,@ctr,1)
            END
            SET @ctr = @ctr + 1
        END
       
    END
RETURN @output
END

This is it guys enjoy.

Read more »

SQL Datetime Format

Let's take a look at the Datetime format of SQL, Below is the T-SQL How SQL deals with datetime.

Format	Query
USA mm/dd/yy	select convert(varchar, getdate(), 1)
ANSI yy.mm.dd	select convert(varchar, getdate(), 2)
British/French dd/mm/yy	select convert(varchar, getdate(), 3)
German dd.mm.yy	select convert(varchar, getdate(), 4)
Italian dd-mm-yy	select convert(varchar, getdate(), 5)
dd mon yy	select convert(varchar, getdate(), 6)
Mon dd, yy	select convert(varchar, getdate(), 7)
USA mm-dd-yy	select convert(varchar, getdate(), 10)
JAPAN yy/mm/dd	select convert(varchar, getdate(), 11)
ISO yymmdd	select convert(varchar, getdate(), 12)
mon dd yyyy hh:miAM (or PM)	select convert(varchar, getdate(), 100)
mm/dd/yyyy	select convert(varchar, getdate(), 101)
yyyy.mm.dd	select convert(varchar, getdate(), 102)
dd/mm/yyyy	select convert(varchar, getdate(), 103)
dd.mm.yyyy	select convert(varchar, getdate(), 104)
dd-mm-yyyy	select convert(varchar, getdate(), 105)
dd mon yyyy	select convert(varchar, getdate(), 106)
Mon dd, yyyy	select convert(varchar, getdate(), 107)
hh:mm:ss	select convert(varchar, getdate(), 108)
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)	select convert(varchar, getdate(), 109)
mm-dd-yyyy	select convert(varchar, getdate(), 110)
yyyy/mm/dd	select convert(varchar, getdate(), 111)
yyyymmdd	select convert(varchar, getdate(), 112)
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)	select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13)
hh:mi:ss:mmm(24h)	select convert(varchar, getdate(), 114)

Read more »

SQL Case Syntax

Here is simple way to using case in your T-SQL

select

CASE
when count(accounts) < 1 then 'Incomplete'
when count(accounts) = 1 then 'One Login'
when count(accounts) = 2 then 'Two Login'
when count(accounts) = 3 then 'Three Login'
when count(accounts) > 3 then 'Complete'

END LoginInfo
from tblemplogin

Read more »

Import Bulk Data into SQL Table From CSV File or TSV File

Create Table tbl_CSVTSVTest
(
id int, name varchar(20), city varchar(20)
)

bulk
insert tbl_CSVTSVTest
from 'd:\testcsv.txt'
with
(
FieldTerminator = ',', -- for tsv file use FieldTerminator = '    '
RowTerminator = '\n'
)
go



select * from tbl_CSVTSVTest

Read more »

SQL SERVER – Difference Between Rebuild Index and Reorganize Index with T-SQL Script

Index Rebuild : This process drops the existing Index and Recreates the index.

USE TESTDB; 

GO 

ALTER INDEX ALL ON Production.Product REBUILD 

GO 

Index Reorganize : This process physically reorganizes the leaf nodes of the index. 

USE TESTDB; 

GO

ALTER INDEX ALL ON Production.Product REORGANIZE 

GO

Read more »