Archive

Archive for the ‘TSQL’ Category

T-SQL 2012 New Functions

June 20, 2016 Leave a comment

 

Date and time functions

  1. DATEFROMPARTS

Syntax:- DATEFROMPARTS(Year, Month, Day)

Arguments:- year Integer expression specifying a year.

                      month Integer expression specifying a month, from 1 to 12.

                      day     Integer expression specifying a day.

Return Type:– Date

DATEFROMPARTS

2. DATETIMEFROMPARTS

Syntax:- DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Arguments:-

year Integer expression specifying a year.

  month  Integer expression specifying a month.

  day      Integer expression specifying a day.

  hour     Integer expression specifying hours.

  minute Integer expression specifying minutes.

  seconds Integer expression specifying seconds.

  milliseconds Integer expression specifying milliseconds.

Return Type:- datetime

DATETIMEFROMPARTS

3. DATETIME2FROMPARTS

Syntax:- DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Arguments:- year Integer expression specifying a year.

  month  Integer expression specifying a month.

  day      Integer expression specifying a day.

  hour     Integer expression specifying hours.

  minute Integer expression specifying minutes.

  seconds Integer expression specifying seconds.

  milliseconds Integer expression specifying milliseconds.

Return Type:- datetime

DATETIME2FROMPARTS

4.SMALLDATETIMEFROMPARTS

Syntax:- SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

Arguments:-

  Year – Integer expression specifying a year.

  Month-Integer expression specifying a month.

  Day Integer expression specifying a day.

  Hour Integer expression specifying hours.

    Minute Integer expression specifying minutes.

Return Type:- smalldatetime

SMALLDATETIMEFROMPARTS

5.DATETIMEOFFSETFROMPARTS

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

6.TIMEFROMPARTS

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

7.EOMONTH 

EOMONTH ( start_date [, month_to_add ] )

EOMONTH

  • Logical functions

    1. CHOOSE ( index, val_1, val_2 [, val_n ] ) 

      CHOOSE

    2. IIF ( boolean_expression, true_value, false_value )SELECT IIF ( 20 > 10, ‘true’, ‘false’ ) AS Result;

      SELECT IIF ( 45 > 30, NULL, NULL ) AS Result;

      DECLARE @P INT = NULL, @S INT = NULL;

      SELECT IIF ( 45 > 30, @p, @s ) AS Result;

  • String functions

    1. CONCAT ( string_value1, string_value2 [, string_valueN ] )CONCAT

    2. FORMAT ( value, format [, culture ] )FORMAT
  • Conversion functions

    1. PARSE ( string_value AS data_type [ USING culture ] )PARSE
    2. TRY_PARSE ( string_value AS data_type [ USING culture ] )TRY_PARSE
    3. TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )TRY_CONVERT
  • Sequence

    Sequence Objects

  • Offset

First mark the start of the row by using “OFFSET” command.

Second specify how many rows you want to fetch by using “FETCH” command.

offset

Hope you like this post.

Please provide your valuable feedback through liking the post or adding comments. It will help to encourage to do lot of similar stuff.

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

Advertisements

SQL Tip #2 How to Find First Day of Week /Month/Quarter/Year – How is work actually?

March 16, 2016 Leave a comment

TSQL Tip:-How to Find First Day of Week /Month/Quarter/Year – How is work actually

We come across always to find first day of Week /Month/Quarter/Year.

We simply google it and find the result and modify the result given by google as per our requirements.

Do we check how actually it works in TSQL?

Let’s first find the First Day of Week/Month/Quarter/Year, and then we analyze how it works.

First Day of Week:-

SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0) FirstDayOfWeek

FirstDayOfweek

How it works?

First we applying DATEDIFF(WEEK,0,GETDATE()), which gives number of week from today’s date to ‘0’ Date.

What is ‘0’ Date, by default when we put 0 then it converts into date as ’01/01/1900′

We can test by executing below statements:-

ZeroConversation

In above screen both queries giving same number of weeks.

After getting number of weeks we will add same number of weeks into 0 , i.e. date ’01/01/1900′

FirstDayOfweek_Breakdown

Anything added to first day of default date based on week then it will give first day week of the current week.

Let’s do same thing for Month.

Month Difference:-

MonthDifference

After difference add 1394 months into 0 or ‘01/01/1900’

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)FirstDayOfMonth1

OR

SELECT DATEADD(MONTH,DATEDIFF(MONTH,’01/01/1900′,GETDATE()),0)FirstDayOfMonth2

FirstDayof Month

Same logic applied on Quarter and Year

First Day of quarter

SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,GETDATE()),0)FirstDayOfQuarter1

OR

SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,’01/01/1900′,GETDATE()),0)FirstDayOfQuarter2

FirstDayofQuarter.png

 

First Day of Year

SELECT DATEADD(Year,DATEDIFF(Year,0,GETDATE()),0)FirstDayOfYear1

OR

 

SELECT DATEADD(Year,DATEDIFF(Year,’01/01/1900′,GETDATE()),’01/01/1900′)FirstDayOfYear2

FirstDayofYear

No more google 🙂 It will help to remember when we know how it works 🙂

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

 

SQL Tip #1:- Dynamic SQL hold SQL query in the variable must have NVARCHAR Data type

January 20, 2015 Leave a comment

In Dynamic SQL query the Query which is stored in the variable should have NVARCHAR Data type.

Let see:-

When we run the below query with data type VARCHAR

DECLARE

@Sql varchar(max)=”

,@Region Varchar(50)=”’IND”,”USA”’

SET @Sql=’SELECT C.CountryName,sum(F.SalesAmount) SalesAmount

FROM DBO.FactSales F

INNER JOIN DimCountry C

ON F.CountryID=C.CountryID

WHERE C.CountryCode in (‘ +@Region +’)

GROUP BY C.CountryName’

EXEC sp_executesql @sql

 

Above Query gives below error:-

Result: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.

 

When we run the below query with data type NVARCHAR

DECLARE

@Sql Nvarchar(max)=”

,@Region Varchar(50)=”’IND”,”USA”’

SET @Sql=’SELECT C.CountryName,sum(F.SalesAmount) SalesAmount

FROM DBO.FactSales F

INNER JOIN DimCountry C

ON F.CountryID=C.CountryID

WHERE C.CountryCode in (‘ +@Region +’)

GROUP BY C.CountryName’

EXEC sp_executesql @sql

Above query gives output:-

Result:-

CountryName SalesAmount
INDIA 2500
UNITED STATES OF AMERICA 9000

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: TSQL Tags: ,

SQL Server 2008 TSQL New Features

October 16, 2012 Leave a comment

SQL Server 2008 New Features introduced

To know more on this topic and resolution please go to T-SQL-SQL-server-2008-tsql-new-features

Topic added in this website http://msbitips.com

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: TSQL Tags: