Friday, December 30, 2011
SQL Server - Split a Text String into a table
So, I work for a car insurance company. I do lots of ETL work. ETL = Extract Transform and Load.
I regularly receive lists of vehicles. These lists are not formatted nicely.
Generally, all the vehicle information for that vehicle is in one column. But that doesn't really work to well for me. I need to separate the Year, Make and Model out into their own columns.
This function has been one of the most useful functions I have run across for this specific task. I'll pass in the vehicle information as the string input and it will return a table full of rows, one for each item in the vehicle. It does this based upon the delimiter passed in as the second variable.
I can then use that result set to properly place the information into their appropriate columns in my tables.
-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID(N'dbo.xSplitTextStringIntoTable',N'TF') IS NOT NULL
BEGIN
PRINT 'Function Found... Dropping'
DROP FUNCTION [dbo].[xSplitTextStringIntoTable]
END
GO
--Create the function
CREATE FUNCTION [dbo].[xSplitTextStringIntoTable]
(
@Input NVARCHAR(MAX)
, @Delimiter NVARCHAR(MAX)
)
RETURNS @Return TABLE
(
ID INT IDENTITY(1,1)
, Value NVARCHAR(MAX)
)
AS
---------------------------------------------------------------------------
-- Receives:
-- @Input
-- :The string of values we wish to break up
-- @Delimiter
-- :tells how text is to be split
-- usually a space or comma, I really like the Pipe
-- delimiter '|'
--
--
-- Returned: Table structure with each delimited word returned as a row
-- i.e. '1999 Dodge Viper'
-- returns 3 rows; (1999, Dodge, Viper ) each with its own row
---------------------------------------------------------------------------
BEGIN
DECLARE @Iterator INT
DECLARE @Index INT
-- We are going to iterate through each item passed in.
-- An item is based upon the delimiter used
SET @Iterator = 1
-- Get the index of the first item
SET @Index = CHARINDEX( @Delimiter, @Input )
--If there are more than one item it will go through this loop
--IF NOT it will skip the while loop
WHILE ( @Index > 0 )
BEGIN
-- Insert the item into our table
INSERT INTO @Return ( Value )
SELECT
Value = LTRIM(RTRIM(
SUBSTRING(@Input, 1, @Index - 1)
))
-- Now remove item in the @Input string since we have
-- added it to the table
SET @Input = SUBSTRING(
@Input
, @Index + DATALENGTH(@Delimiter) / 2
, LEN( @Input )
)
-- increment the iterator by one
SET @Iterator = @Iterator + 1
-- Get the next index number, if none found while loop will end
SET @Index = CHARINDEX( @Delimiter, @Input )
END -- END OF THE WHILE LOOP
-- Here we either
-- enter the lone item passed in
-- or the last item passed in
INSERT INTO @Return (Value)
SELECT Value = LTRIM(RTRIM( @Input ))
RETURN
END
GO
-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
DECLARE @Vehicle NVARCHAR(MAX)
SELECT @Vehicle = '1964 Dodge Dakato Station Wagon'
-- Get the full table of values
SELECT *
FROM [dbo].[xSplitTextStringIntoTable] (@Vehicle, ' ')
-- Or select from it just like a regular table
-- In this case I just want the value of the 3rd item in the list
SELECT Value
FROM [dbo].[xSplitTextStringIntoTable] (@Vehicle, ' ')
Where ID = 3
Thursday, December 29, 2011
SQL Server - Fun With Dates
So, before anyone complains about my method of posting functions. I like functions. I work well with them.
But, hey. Not everyone likes them. You just want the code. If you want a somewhat detailed explanation of what is going on, visit their related posts.
So, below is where I'll keep the simple code snippets, with just an intro explanation...
I'll add to this as I add more posts relating to dates or even before I create the related posts.
I'll try and remember to do the same with the various other items I work with.
Oh, and these may not be the fastest queries or the most readable. But they should work. If you find one that doesn't let me know.
Enjoy...
-- Set the Date you plan on using
DECLARE @Date DateTime
SET @Date = '2011-12-14'
-- Get the Last Day of the Month
SELECT DATEADD(d,-DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@date))
-- Get the Last Day of the Prior Month
SELECT DATEADD(d,-Day(@Date),@Date)
-- Get the First Day of the Month
SELECT DATEADD(d,-Day(@Date) +1 ,@Date)
-- Get the First Day of the prior Month
SELECT DATEADD(d,-DAY(DATEADD(m,-1,@Date)-1 ),DATEADD(m,-1,@date))
-- Get the First Day of the next Month
SELECT DATEADD(d,-DAY(DATEADD(m,1,@Date)-1 ),DATEADD(m,1,@date))
--Get the Last Day of the Week
SELECT DATEADD(d,7-(DATEPART(dw,@Date)),@Date)
--Get the First Day of the Given Week
SELECT DATEADD(d,-(DATEPART(dw, @Date) - 1),@Date)
-- The following will return only the date portion of the
-- DateTime. This strips off the Time and leaves only zeros.
SET @Date = GETDATE() --Get current system date and time
SELECT @Date as OriginalDate
, DATEADD(d,0,DATEDIFF(d,0,@Date)) as StrippedDate
But, hey. Not everyone likes them. You just want the code. If you want a somewhat detailed explanation of what is going on, visit their related posts.
So, below is where I'll keep the simple code snippets, with just an intro explanation...
I'll add to this as I add more posts relating to dates or even before I create the related posts.
I'll try and remember to do the same with the various other items I work with.
Oh, and these may not be the fastest queries or the most readable. But they should work. If you find one that doesn't let me know.
Enjoy...
-- Set the Date you plan on using
DECLARE @Date DateTime
SET @Date = '2011-12-14'
-- Get the Last Day of the Month
SELECT DATEADD(d,-DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@date))
-- Get the Last Day of the Prior Month
SELECT DATEADD(d,-Day(@Date),@Date)
-- Get the First Day of the Month
SELECT DATEADD(d,-Day(@Date) +1 ,@Date)
-- Get the First Day of the prior Month
SELECT DATEADD(d,-DAY(DATEADD(m,-1,@Date)-1 ),DATEADD(m,-1,@date))
-- Get the First Day of the next Month
SELECT DATEADD(d,-DAY(DATEADD(m,1,@Date)-1 ),DATEADD(m,1,@date))
--Get the Last Day of the Week
SELECT DATEADD(d,7-(DATEPART(dw,@Date)),@Date)
--Get the First Day of the Given Week
SELECT DATEADD(d,-(DATEPART(dw, @Date) - 1),@Date)
-- The following will return only the date portion of the
-- DateTime. This strips off the Time and leaves only zeros.
SET @Date = GETDATE() --Get current system date and time
SELECT @Date as OriginalDate
, DATEADD(d,0,DATEDIFF(d,0,@Date)) as StrippedDate
Labels:
Date,
DateAdd,
DatePart,
Function,
SQL 2000,
SQL 2005,
SQL 2008 R2,
SQL Server,
T-SQL,
TSQL
SQL Server - Get Last Day of a given Month
This function will return the last day of a given month based upon the date passed in.
-------------------------------------------------
--Create Function to return the last day of the
--week based upon the date passed in.
--
--You pass in a Date and it return
--the Date for last day of the week
-------------------------------------------------
CREATE FUNCTION [dbo].[xGetLastDayOfMonth](@Date DateTime)
RETURNS DateTime
AS
BEGIN
DECLARE @DayOfMonth INT
DECLARE @AddOneMonthToDate DateTime
-- Add one month to the date passed in
SET @AddOneMonthToDate = DateAdd(m,1,@Date)
-- Get the day number of @AddOneMonthToDate
-- When adding one Month to the passed in Date
-- we could end up with a different day than
-- the original month. Add one month to 1/31/2011
-- and we get 2/28/2011. We want to make sure
-- we get day number in the next month.
SET @DayOfMonth = DAY(@AddOneMonthToDate)
-- We will now subtract the @DayOfMonth
-- from the @AddOneMonthToDate. Since there is no
-- such thing as a month with a ZERO, it rolls
-- back to the last day of the prior month.
-- if you were to use 1/29/2000,1/30/2000 or 1/31/2000
-- as the date passed in an we add one month
-- to that we would get 2/28/2000 back. Since
-- we told it to add one month, it only adds one
-- month to and makes sure we get back a valid
-- date.
-- The @dayOfMonth that we then get from this
-- is 28.
-- We then subtract 28 from 2/28/2000.
-- Since we can't have 2/00/2000 it goes to the
-- prior day and the result in 1/31/2000.
RETURN DATEADD(d, -@DayOfMonth, @AddOneMonthToDate)
-- Of course we could just use this one line below
-- to do all the work for us, instead of the previous
-- five lines. But then that wouldn't be fun, eh?
/*
RETURN DATEADD(d
, -DAY( DATEADD(m,1,@date) )
, DATEADD(m,1,@date)
)
*/
END
GO
-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
-- Should return 12/31/2011
Select dbo.xGetLastDayOfMonth('2011-12-25')
-------------------------------------------------
--Create Function to return the last day of the
--week based upon the date passed in.
--
--You pass in a Date and it return
--the Date for last day of the week
-------------------------------------------------
CREATE FUNCTION [dbo].[xGetLastDayOfMonth](@Date DateTime)
RETURNS DateTime
AS
BEGIN
DECLARE @DayOfMonth INT
DECLARE @AddOneMonthToDate DateTime
-- Add one month to the date passed in
SET @AddOneMonthToDate = DateAdd(m,1,@Date)
-- Get the day number of @AddOneMonthToDate
-- When adding one Month to the passed in Date
-- we could end up with a different day than
-- the original month. Add one month to 1/31/2011
-- and we get 2/28/2011. We want to make sure
-- we get day number in the next month.
SET @DayOfMonth = DAY(@AddOneMonthToDate)
-- We will now subtract the @DayOfMonth
-- from the @AddOneMonthToDate. Since there is no
-- such thing as a month with a ZERO, it rolls
-- back to the last day of the prior month.
-- if you were to use 1/29/2000,1/30/2000 or 1/31/2000
-- as the date passed in an we add one month
-- to that we would get 2/28/2000 back. Since
-- we told it to add one month, it only adds one
-- month to and makes sure we get back a valid
-- date.
-- The @dayOfMonth that we then get from this
-- is 28.
-- We then subtract 28 from 2/28/2000.
-- Since we can't have 2/00/2000 it goes to the
-- prior day and the result in 1/31/2000.
RETURN DATEADD(d, -@DayOfMonth, @AddOneMonthToDate)
-- Of course we could just use this one line below
-- to do all the work for us, instead of the previous
-- five lines. But then that wouldn't be fun, eh?
/*
RETURN DATEADD(d
, -DAY( DATEADD(m,1,@date) )
, DATEADD(m,1,@date)
)
*/
END
GO
-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
-- Should return 12/31/2011
Select dbo.xGetLastDayOfMonth('2011-12-25')
SQL Server - Get the Last day of a given week
So how do we find the last day of the week for a given date?
-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID(N'dbo.xGetLastDayOfWeek',N'FN') IS NOT NULL
BEGIN
print 'Function found... Dropping'
DROP FUNCTION dbo.xGetLastDayOfWeek
END
GO
CREATE FUNCTION dbo.xGetLastDayOfWeek(@Date DateTime)
RETURNS DateTime
AS
-------------------------------------------------
--Function will return the last day of the
--week based upon the date passed in.
--
--You pass in a Date and it return
--the Date for last day of the week
-------------------------------------------------
BEGIN
DECLARE @DayOfWeek INT
DECLARE @DaysToAdd INT
-- Get the day of the week
-- i.e. if today is Thursday and the week starts on Sunday
-- then the day of the week is 5
SET @DayOfWeek = DatePart( dw, @Date )
-- We need to subtract the @DayOfWeek from 7.
-- This will tell us how many days we need to add to the
-- current Date to find the end of the week.
SET @DaysToAdd = 7 - @DayOfWeek
-- Now we add @@DaysToAdd to the @date passed in.
-- This will return the last day of the week for the given @date.
RETURN DATEADD( d, @DaysToAdd, @Date )
-- Instead of the previous five lines of code we could have also
-- used this single line. I broke it out above to hopefully better
-- explain what was happening.
--RETURN DATEADD(d,7-(DATEPART(dw, @Date) ),@Date)
END
GO
-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
-- Should return 12/03/2011
Select dbo.xGetLastDayOfWeek('2011-12-01')
-- Should return 12/31/2011
Select dbo.xGetLastDayOfWeek('2011-12-25')
-- Should return 12/31/2011
Select dbo.xGetLastDayOfWeek('2011-12-31')
-- Does the function already exist?
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID(N'dbo.xGetLastDayOfWeek',N'FN') IS NOT NULL
BEGIN
print 'Function found... Dropping'
DROP FUNCTION dbo.xGetLastDayOfWeek
END
GO
CREATE FUNCTION dbo.xGetLastDayOfWeek(@Date DateTime)
RETURNS DateTime
AS
-------------------------------------------------
--Function will return the last day of the
--week based upon the date passed in.
--
--You pass in a Date and it return
--the Date for last day of the week
-------------------------------------------------
BEGIN
DECLARE @DayOfWeek INT
DECLARE @DaysToAdd INT
-- Get the day of the week
-- i.e. if today is Thursday and the week starts on Sunday
-- then the day of the week is 5
SET @DayOfWeek = DatePart( dw, @Date )
-- We need to subtract the @DayOfWeek from 7.
-- This will tell us how many days we need to add to the
-- current Date to find the end of the week.
SET @DaysToAdd = 7 - @DayOfWeek
-- Now we add @@DaysToAdd to the @date passed in.
-- This will return the last day of the week for the given @date.
RETURN DATEADD( d, @DaysToAdd, @Date )
-- Instead of the previous five lines of code we could have also
-- used this single line. I broke it out above to hopefully better
-- explain what was happening.
--RETURN DATEADD(d,7-(DATEPART(dw, @Date) ),@Date)
END
GO
-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
-- Should return 12/03/2011
Select dbo.xGetLastDayOfWeek('2011-12-01')
-- Should return 12/31/2011
Select dbo.xGetLastDayOfWeek('2011-12-25')
-- Should return 12/31/2011
Select dbo.xGetLastDayOfWeek('2011-12-31')
Labels:
Date,
DateAdd,
DatePart,
Function,
SQL 2000,
SQL 2005,
SQL 2008 R2,
SQL Server,
T-SQL,
TSQL
SQL Server - Get the First day of a given week
The first of several Date functions that I've created to determine certain dates.
This one will return the first day of a week based upon the date you pass in.
-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID(N'dbo.xGetFirstDayOfWeek',N'FN') IS NOT NULL
BEGIN
print 'Function found... Dropping'
DROP FUNCTION dbo.xGetFirstDayOfWeek
END
GO
CREATE FUNCTION dbo.xGetFirstDayOfWeek(@Date DateTime)
RETURNS DateTime
AS
-------------------------------------------------
--Function will return the first day of the
--week based upon the date passed in.
--
--You pass in a Date and it return
--the Date for first day of the week
-------------------------------------------------
BEGIN
DECLARE @DayOfWeek INT
DECLARE @DaysToSubtract INT
-- Get the day of the week
-- i.e. if today is Thursday and the week starts on Sunday
-- then the day of the week is 5
SET @DayOfWeek = DatePart( dw, @Date )
-- We need to subtract one from the @DayOfWeek.
-- The reason is that if today is the 5th day and we want
-- to get back to the 1st day, we only need to subtract 4
-- from the Date. Otherwise we would end up at 0.
SET @DaysToSubtract = @DayOfWeek -1
-- Now we subtract @DaysToSubtract from the @date passed in.
-- This will return the first day of the week for the given @date.
RETURN DATEADD( d, -@DaysToSubtract, @Date )
-- Instead of the previous five lines of code we could
-- have also used this single line. I broke it out above to
-- hopefully better explain what was happening.
-- RETURN DATEADD(d,-(DATEPART(dw, @Date) - 1),@Date)
END
GO
-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
This one will return the first day of a week based upon the date you pass in.
-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID(N'dbo.xGetFirstDayOfWeek',N'FN') IS NOT NULL
BEGIN
print 'Function found... Dropping'
DROP FUNCTION dbo.xGetFirstDayOfWeek
END
GO
CREATE FUNCTION dbo.xGetFirstDayOfWeek(@Date DateTime)
RETURNS DateTime
AS
-------------------------------------------------
--Function will return the first day of the
--week based upon the date passed in.
--
--You pass in a Date and it return
--the Date for first day of the week
-------------------------------------------------
BEGIN
DECLARE @DayOfWeek INT
DECLARE @DaysToSubtract INT
-- Get the day of the week
-- i.e. if today is Thursday and the week starts on Sunday
-- then the day of the week is 5
SET @DayOfWeek = DatePart( dw, @Date )
-- We need to subtract one from the @DayOfWeek.
-- The reason is that if today is the 5th day and we want
-- to get back to the 1st day, we only need to subtract 4
-- from the Date. Otherwise we would end up at 0.
SET @DaysToSubtract = @DayOfWeek -1
-- Now we subtract @DaysToSubtract from the @date passed in.
-- This will return the first day of the week for the given @date.
RETURN DATEADD( d, -@DaysToSubtract, @Date )
-- Instead of the previous five lines of code we could
-- have also used this single line. I broke it out above to
-- hopefully better explain what was happening.
-- RETURN DATEADD(d,-(DATEPART(dw, @Date) - 1),@Date)
END
GO
--Now we will check to see if the code works
-------------------------------------------------
-- Should return 11/27/2011
Select dbo.xGetFirstDayOfWeek('2011-12-01')
-- Should return 12/25/2011 --already the first day of the week
Select dbo.xGetFirstDayOfWeek('2011-12-25')
Select dbo.xGetFirstDayOfWeek('2011-12-25')
Labels:
Date,
DateAdd,
DatePart,
Function,
SQL 2000,
SQL 2005,
SQL 2008 R2,
SQL Server,
T-SQL,
TSQL
Monday, November 21, 2011
SQL Server - Check for Leap Year (Part 2)
And here is the second part to check for leap year. This uses the standard algorithm to check for leap year.
For more information about Leap Year: Leap Year on Wikipedia
-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.
-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID(N'dbo.xIsLeapYear2',N'FN') IS NOT NULL
BEGIN
print 'Function found... Dropping'
DROP FUNCTION dbo.xIsLeapYear2
END
GO
CREATE FUNCTION xIsLeapYear2(@Year SmallInt)
RETURNS CHAR(3)
AS
-------------------------------------------------
--Function will Check to See if the year
--passed in is a leap year.
--
--This function uses an Algorithm to get the job done.
--
--You pass in a Year and it returns 'Yes' or 'No'
-------------------------------------------------
BEGIN
DECLARE @CheckYear Char(3)
SET @CheckYear =
(
SELECT
CASE
-- Is the Year Divisible by 400 with 0 remaining?
WHEN @Year%400 = 0 THEN 'YES'
-- Is the Year Divisible by 100 with 0 remaining?
WHEN @Year%100 = 0 THEN 'NO'
-- Is the Year Divisible by 4 with 0 remaining?
WHEN @Year%4 = 0 THEN 'YES'
ELSE
'NO'
END
)
RETURN @CheckYear
END
GO
BEGIN
print 'Function found... Dropping'
DROP FUNCTION dbo.xIsLeapYear2
END
GO
CREATE FUNCTION xIsLeapYear2(@Year SmallInt)
RETURNS CHAR(3)
AS
-------------------------------------------------
--Function will Check to See if the year
--passed in is a leap year.
--
--This function uses an Algorithm to get the job done.
--
--You pass in a Year and it returns 'Yes' or 'No'
-------------------------------------------------
BEGIN
DECLARE @CheckYear Char(3)
SET @CheckYear =
(
SELECT
CASE
-- Is the Year Divisible by 400 with 0 remaining?
WHEN @Year%400 = 0 THEN 'YES'
-- Is the Year Divisible by 100 with 0 remaining?
WHEN @Year%100 = 0 THEN 'NO'
-- Is the Year Divisible by 4 with 0 remaining?
WHEN @Year%4 = 0 THEN 'YES'
ELSE
'NO'
END
)
RETURN @CheckYear
END
GO
--Now we will check to see if the code works
-------------------------------------------------
PRINT''
PRINT 'Using the algorithm operator to check leap year.'
PRINT '------------------------------------------------'
PRINT 'Is 1900 a Leap year? ' + dbo.xIsLeapYear2(1900)
PRINT 'Is 2000 a Leap year? ' + dbo.xIsLeapYear2(2000)
PRINT 'Is 2008 a Leap year? ' + dbo.xIsLeapYear2(2008)
PRINT 'Is 2010 a Leap year? ' + dbo.xIsLeapYear2(2010)
Labels:
Date,
Function,
Leap Year,
SQL 2000,
SQL 2005,
SQL 2008 R2,
SQL Server,
T-SQL,
TSQL
SQL Server - Check for Leap Year (Part 1)
I have this co-worker who is obsessed about leap year. This post is dedicated to him.
There are various ways to determine if a given year is a leap year. The first function here is using the various SQL Date functions to get the job done.
-------------------------------------------------
-- Does the function already exist?
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID( N'dbo.xIsLeapYear',N'FN' ) IS NOT NULL
BEGIN
print 'Function found... Dropping'
DROP FUNCTION dbo.xIsLeapYear
END
GO
CREATE FUNCTION xIsLeapYear(@Year SmallInt)
RETURNS CHAR(3)
AS
-------------------------------------------------
--Function will Check to See if the year
--passed in is a leap year.
--
--This function uses SQL Server Date functions
--to get the job done.
--
--You pass in a Year and it returns 'Yes' or 'No'
-------------------------------------------------
BEGIN
DECLARE @Date DateTime
DECLARE @Result Char(3)
-- Turn the Passed in year to Feb 28th
SET @Date = Convert( Varchar(4), @Year ) + '0228'
-- Add one Day to the date
SET @Date = DateAdd( d, 1, @Date )
-- Is the @date variable still February?
IF ( DatePart(M, @date) = 2 )
BEGIN
SET @Result = 'Yes'
END
ELSE
BEGIN
SET @Result = 'NO'
END
RETURN @Result
END
GO
BEGIN
print 'Function found... Dropping'
DROP FUNCTION dbo.xIsLeapYear
END
GO
CREATE FUNCTION xIsLeapYear(@Year SmallInt)
RETURNS CHAR(3)
AS
-------------------------------------------------
--Function will Check to See if the year
--passed in is a leap year.
--
--This function uses SQL Server Date functions
--to get the job done.
--
--You pass in a Year and it returns 'Yes' or 'No'
-------------------------------------------------
BEGIN
DECLARE @Date DateTime
DECLARE @Result Char(3)
-- Turn the Passed in year to Feb 28th
SET @Date = Convert( Varchar(4), @Year ) + '0228'
-- Add one Day to the date
SET @Date = DateAdd( d, 1, @Date )
-- Is the @date variable still February?
IF ( DatePart(M, @date) = 2 )
BEGIN
SET @Result = 'Yes'
END
ELSE
BEGIN
SET @Result = 'NO'
END
RETURN @Result
END
GO
-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
PRINT ''
PRINT 'Using SQL Server Date functions to check Year.'
PRINT '----------------------------------------------'
PRINT 'Is 1900 a Leap year? ' + dbo.xIsLeapYear(1900)
PRINT 'Is 2000 a Leap year? ' + dbo.xIsLeapYear(2000)
PRINT 'Is 2008 a Leap year? ' + dbo.xIsLeapYear(2008)
PRINT 'Is 2010 a Leap year? ' + dbo.xIsLeapYear(2010)
--Now we will check to see if the code works
-------------------------------------------------
PRINT ''
PRINT 'Using SQL Server Date functions to check Year.'
PRINT '----------------------------------------------'
PRINT 'Is 1900 a Leap year? ' + dbo.xIsLeapYear(1900)
PRINT 'Is 2000 a Leap year? ' + dbo.xIsLeapYear(2000)
PRINT 'Is 2008 a Leap year? ' + dbo.xIsLeapYear(2008)
PRINT 'Is 2010 a Leap year? ' + dbo.xIsLeapYear(2010)
Labels:
Date,
DateAdd,
Function,
Leap Year,
SQL 2000,
SQL 2005,
SQL 2008 R2,
SQL Server,
T-SQL,
TSQL
Sunday, November 20, 2011
SQL Server 2012
I'm very excited to see that the SQL Server 2012 RC0 was recently pushed out. This has also been known with the code name of "Denali".
Since I mainly work with the Data warehousing and Business Intelligence side of things, I'll post some of the things that caught my attention...
Microsoft has introduced a new Semantic Model (BISM) to improve Excel, Reporting Services and the SharePoint experience.It appears that the new Semantic Model is in addition to the current BI Models, so you won't be forced to upgrade your existing stack at launch time.
Since I mainly work with the Data warehousing and Business Intelligence side of things, I'll post some of the things that caught my attention...
Microsoft has introduced a new Semantic Model (BISM) to improve Excel, Reporting Services and the SharePoint experience.It appears that the new Semantic Model is in addition to the current BI Models, so you won't be forced to upgrade your existing stack at launch time.
PowerPivot
- Supports Hierachies
- Multiple relationships between tables
- easily create, edit and manage measures
- Key Performance Indicators (KPIs) <-- Nice!
- new DAX functions
- Time series, distinct counts
- The above items from the PowerPivot
- In addition to row-level security
- partitions
- Direct Query Mode
- enables access of information directly form the source system.
Saturday, November 19, 2011
SQL Server - Create indexes on Temp Tables
-------------------------------------------------
--Create temp table to hold values
-------------------------------------------------
CREATE TABLE #myTempTable
(
ID int
, customer varchar(50)
)
--Create temp table to hold values
-------------------------------------------------
CREATE TABLE #myTempTable
(
ID int
, customer varchar(50)
)
-------------------------------------------------
--Create Clustered Index on the ID
-------------------------------------------------
--Create Clustered Index on the ID
-------------------------------------------------
CREATE CLUSTERED INDEX #IX_myTempTable_ID
ON #myTempTable ( ID )
ON #myTempTable ( ID )
-------------------------------------------------
--Create Index on customer
-------------------------------------------------
--Create Index on customer
-------------------------------------------------
CREATE INDEX #IX_myTempTable_Customer
ON #myTempTable ( customer )
ON #myTempTable ( customer )
-- Note: When you drop the table,
-- the indexes will be dropped at the same time.
SQL Server - Check to see if a temp table already exists
-------------------------------------------------
--Check to see if temp table exists
-------------------------------------------------
IF OBJECT_ID('tempdb..#myTempTable') IS NOT NULL
BEGIN
PRINT '#myTempTable Already Exists... Dropping'
DROP TABLE #myTempTable
END
ELSE
BEGIN
PRINT '#myTempTable does not yet exist...'
END
-------------------------------------------------
--Create temp table to hold values
-------------------------------------------------
CREATE TABLE #myTempTable
(
ID int
, customer varchar(50)
)
PRINT '#myTempTable Created'
--Check to see if temp table exists
-------------------------------------------------
IF OBJECT_ID('tempdb..#myTempTable') IS NOT NULL
BEGIN
PRINT '#myTempTable Already Exists... Dropping'
DROP TABLE #myTempTable
END
ELSE
BEGIN
PRINT '#myTempTable does not yet exist...'
END
-------------------------------------------------
--Create temp table to hold values
-------------------------------------------------
CREATE TABLE #myTempTable
(
ID int
, customer varchar(50)
)
PRINT '#myTempTable Created'
SQL Server - Check to see if a function already exists
So, you need to know if a function already exists in your database? The code you seek is below.
Need to know how to create that function as well? I've created a useless function just for you.
Need to know how to create that function as well? I've created a useless function just for you.
-- Does the function already exist?
-- if so... drop it.
--
--
-- This will check for the various types of functions
-- Type of:
-- FN = Scalar Function
-- IF = Inline Table Function
-- TF = Table Function
--
-- If you are working with Assembly CLR Functions then you will
-- need to use:
--
-- FS = Assembly (CLR) Scalar Function
-- FT = Assembly (CLR) Table Valued Function
-------------------------------------------------IF EXISTS
(
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.xTestFunction')
AND type in (N'FN', N'IF', N'TF')
)
BEGIN
PRINT 'function found... Dropping'
DROP FUNCTION dbo.xTestFunction
END
ELSE
BEGIN
PRINT 'function does not yet exist...'
END
GO
-------------------------------------------------
--Create a useless Test Function
-- Pass in a text string
-- Get the same text string back
-------------------------------------------------
CREATE FUNCTION dbo.xTestFunction( @Input NVarChar(MAX) )
RETURNS NVarChar(MAX)
AS
BEGIN
DECLARE @Result NVarChar(MAX)
SET @Result = @Input
RETURN @Result
END
GO
-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
Select dbo.xTestFunction('This is my useless text string Input')
(
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.xTestFunction')
AND type in (N'FN', N'IF', N'TF')
)
BEGIN
PRINT 'function found... Dropping'
DROP FUNCTION dbo.xTestFunction
END
ELSE
BEGIN
PRINT 'function does not yet exist...'
END
GO
-------------------------------------------------
--Create a useless Test Function
-- Pass in a text string
-- Get the same text string back
-------------------------------------------------
CREATE FUNCTION dbo.xTestFunction( @Input NVarChar(MAX) )
RETURNS NVarChar(MAX)
AS
BEGIN
DECLARE @Result NVarChar(MAX)
SET @Result = @Input
RETURN @Result
END
GO
-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------
Select dbo.xTestFunction('This is my useless text string Input')
Subscribe to:
Posts (Atom)