Archive for the ‘SQL’ Category

Introduction

Many times there is requirement like to display last activity date and time smartly for last logged in, last message, last chat etc. So, I’ve created a function in MS SQL, which will return last activity date or time. This function will compare last date time with current date time that is ‘GETDATE()’ and will return result accordingly.

Output will be displayed in second, minute(s), hour(s), day(s), month(s), and in year(s) as shown below

———————————————————————————————————————————————————

Query: SELECT dbo.F_GetLastActiveDateTime(GETDATE()-1)

Result: day ago (11-10-2013)

———————————————————————————————————————————————————

Query: SELECT dbo.F_GetLastActiveDateTime(GETDATE()-2)

Result: 2 days ago (11-10-2013)

———————————————————————————————————————————————————

Query: SELECT dbo.F_GetLastActiveDateTime(CONVERT(DATETIME,’2013-10-12 12:04:46.323′))

Result: 2 minutes ago

———————————————————————————————————————————————————

Screenshot

Function smart Date or Time like Google and Facebook in MS SQL

Function to display smart Date or Time like Google and Facebook in MS SQL

Using the Code

CREATE FUNCTION [dbo].[F_GetLastActiveDateTime]
(
    -- Add the parameters for the function here
    @lastActiveDate Datetime
)
RETURNS varchar(30)
AS
    BEGIN
          DECLARE @LastActivity varchar(100)
          SET @LastActivity = '';
         -- Add the T-SQL statements to compute the return value here
         DECLARE @today datetime, @nowLastActiveDate datetime
         DECLARE @years int, @months int, @days int, @hours int, @minutes int, @seconds int, @h int, @m int, @s int 

         SELECT @today = GETDATE() 
         SELECT @nowLastActiveDate = DATEADD(year, DATEDIFF(year, @lastActiveDate, @today), @lastActiveDate) 

         SELECT @years = DATEDIFF(year, @lastActiveDate, @today) - (CASE WHEN @nowLastActiveDate > @today THEN 1 ELSE 0 END) 
         SELECT @months = MONTH(@today - @nowLastActiveDate) - 1 
         SELECT @days = DAY(@today - @nowLastActiveDate) - 1 

         SELECT @h = DATEDIFF(HOUR, @lastActiveDate, @today)
         SELECT @m = DATEDIFF(MINUTE, @lastActiveDate, @today)
         SELECT @s = DATEDIFF(SECOND, @lastActiveDate, @today)

         SET @hours = (@h%24)
         SET @minutes = (@m%60)
         SET @seconds = (@s%60) 

          SET @LastActivity =
         (CASE 
         WHEN @years = 1 THEN ' year ago (' + convert(varchar, @lastActiveDate, 105) + ')'
         WHEN @years > 1 THEN convert(varchar(3),@years) + ' years ago (' + convert(varchar, @lastActiveDate, 105) + ')'
         WHEN @months > 1 THEN ' month ago (' + convert(varchar, @lastActiveDate, 105) + ')'
         WHEN @months = 1 THEN convert(varchar(3),@months) + ' months ago (' + convert(varchar, @lastActiveDate, 105) + ')'
         WHEN @days = 1 THEN ' day ago (' + convert(varchar, @lastActiveDate, 105) + ')'
         WHEN @days > 1 THEN convert(varchar(3),@days) + ' days ago (' + convert(varchar, @lastActiveDate, 105) + ')'
         WHEN @hours = 1 THEN ' hour ago'
         WHEN @hours > 1 THEN convert(varchar(3),@hours) + ' hours ago'
         WHEN @minutes = 1 THEN ' minute ago'
         WHEN @minutes > 1 THEN convert(varchar(3),@minutes) + ' minutes ago'
         WHEN @seconds = 1 THEN ' second ago'
         WHEN @seconds > 1 THEN convert(varchar(3),@seconds) + ' seconds ago'
        ELSE convert(varchar, @lastActiveDate, 105)
    END)

    RETURN @LastActivity;
END
Advertisements

Split comma separated values in MS Sql

Posted: October 10, 2013 in SQL

Introduction

Manytimes there is requirement where we have to send values to Sql stored procedure using parameter. Suppose we have to store value from CheckListBox having 10 items to Database, One way is to run loop one by one and pass parameter to sql stored procedure, in this case system will connect to sql 10 times (in our example). Other way is to create a string with comma(‘,’) separated and pass it to sql stored procedure in single parameter and then split it into sql query.

For that we can use Split function and we can use the data in the way we want.

Split function of SQL Server is not in-built function,We have to add it manually in our Table-valued function as you can find the function below.

Using the Code

CREATE FUNCTION [dbo].[Split] 
(
     @InputString VARCHAR(8000),
     @Delimiter VARCHAR(50)
)
RETURNS @Items TABLE 
(
     Item VARCHAR(8000)
)
AS
BEGIN
     IF @Delimiter = ' '
     BEGIN
          SET @Delimiter = ','
          SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
     END
     IF (@Delimiter IS NULL OR @Delimiter = '')
     SET @Delimiter = ','
     --INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
     --INSERT INTO @Items VALUES (@InputString) -- Diagnostic
     DECLARE @Item VARCHAR(8000)
     DECLARE @ItemList VARCHAR(8000)
     DECLARE @DelimIndex INT
     SET @ItemList = @InputString
     SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
     WHILE (@DelimIndex != 0)
     BEGIN
          SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
          INSERT INTO @Items VALUES (@Item)
          -- Set @ItemList = @ItemList minus one less item
          SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
          SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
     END -- End WHILE
     IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
     BEGIN
          SET @Item = @ItemList
          INSERT INTO @Items VALUES (@Item)
     END
     -- No delimiters were encountered in @InputString, so just return @InputString
     ELSE INSERT INTO @Items VALUES (@InputString)
     RETURN
     END -- End Function

How to Use ?

Create above function in MS SQL, it is a Table Valued Function.

Ex. if there are 10 items like item1, item2,……item10 then

SELECT item FROM dbo.split(‘item1,item2,item3,item4,item5,item6,item7,item8,item9,item10′,’,’)

Output will be

item1
item2
item3
item4
item5
item6
item7
item8
item9
item10

Screenshot

Function to Split comma separated values in MS Sql

Function to Split comma separated values in MS Sql

Get Database Size in MS SQL Server

Posted: October 5, 2013 in SQL

MS SQL Database Size in KB, MB and GB

There are couple of different ways of get Database size. One of them is given below which shows the DB Id, Database Name, Database Logical Name, File Size in KB, MB and GB. This shows size for both .mdf and .ldf file.

SELECT
     db.[dbid] as 'DB ID',
     db.[name] as 'Database Name',
     af.[name] as 'Database Logical Name',
     af.[size] * 8 as 'File Size (KB)',
     CAST((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) AS DECIMAL(18,4)) as 'File Size (MB)',
     CAST(((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) AS DECIMAL(18,4)) as 'File Size (GB)', 
     af.[filename] as 'Physical Name'
FROM 
     SYS.SYSDATABASES db
     INNER JOIN SYS.SYSALTFILES af ON db.dbid = af.dbid

Function to Get Current Age in MS SQL

Posted: October 1, 2013 in SQL

Introduction

Many times we need to calculate user or client age in our requirement. So, I’ve created a function to calculate someone’s age in years, months, and days (best way).

Screenshot

Calculate User Age in SQL

Calculate User Age in SQL

Using the Code

Copy and page the below function in SQL and you can use it as show below just you have to pass only DATE to function.

—————————————————————————————–

SELECT dbo.f_getage(CONVERT(DATETIME,’19/07/1983′,105))

—————————————————————————————–

CREATE FUNCTION [dbo].[F_GetAge]
(
      -- Add the parameters for the function here
      @dayOfBirth Datetime
)
RETURNS varchar(50)
AS
BEGIN
      -- Declare the return variable here
      DECLARE @Age varchar(50)
      SET @Age = '';
      -- Add the T-SQL statements to compute the return value here
      DECLARE @today datetime, @thisYearBirthDay datetime
      DECLARE @years int, @months int, @days int 
      SELECT @today = GETDATE() 
      SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth) 
      SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END) 
      SELECT @months = MONTH(@today - @thisYearBirthDay) - 1 
      SELECT @days = DAY(@today - @thisYearBirthDay) - 1 
      SET @Age = convert(varchar(3),@years) + ' Years ' + convert(varchar(3),@months) + ' Months ' + convert(varchar(3),@days) + ' days'
     -- Return the result of the function
     RETURN @Age
END

Problem
There are many instances when dates and times don’t show up at your doorstep in the format you’d like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Different Data Format in MS SQL

Different Data Format in MS SQL

Solution
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, then getutcdate() should be used. To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed. Below is a list of formats and an example of the output:

Format Query Output
1 select convert(varchar, getdate(), 1) 09/27/13
2 select convert(varchar, getdate(), 2) 13.09.27
3 select convert(varchar, getdate(), 3) 27/09/13
4 select convert(varchar, getdate(), 4) 27.09.13
5 select convert(varchar, getdate(), 5) 27-09-13
6 select convert(varchar, getdate(), 6) 27 Sep 13
7 select convert(varchar, getdate(), 7) Sep 27, 13
10 select convert(varchar, getdate(), 10) 09-27-13
11 select convert(varchar, getdate(), 11) 13/09/27
101 select convert(varchar, getdate(), 101) 09/27/2013
102 select convert(varchar, getdate(), 102) 2013.09.27
103 select convert(varchar, getdate(), 103) 27/09/2013
104 select convert(varchar, getdate(), 104) 27.09.2013
105 select convert(varchar, getdate(), 105) 27-09-2013
106 select convert(varchar, getdate(), 106) 27 Sep 2013
107 select convert(varchar, getdate(), 107) Sep 27, 2013
110 select convert(varchar, getdate(), 110) 09-27-2013
111 select convert(varchar, getdate(), 111) 2013/09/27

Different Time Formats

Format Query Output
8 or 108 select convert(varchar, getdate(), 8) 00:38:54
9 or 109 select convert(varchar, getdate(), 9) Sep 27 2013 12:38:54:840AM
14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840

Get YEAR, MONTH and DAY

SELECT YEAR(GETDATE()) –2013
SELECT MONTH(GETDATE()) –09
SELECT DAY(GETDATE()) –27

Get Year Month Day in MS SQL

Get Year Month Day in MS SQL

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement Output
select replace(convert(varchar, getdate(),101),’/’,”) 12302006
select replace(convert(varchar, getdate(),101),’/’,”) + replace(convert(varchar, getdate(),108),’:’,”) 12302006004426