SQL Server Function to merge a date with a time

I use this when I need to join two fields. One has a date, the other has a time.   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_MergeDate2Time]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) DROP FUNCTION [util].[uf_MergeDate2Time] GO CREATE FUNCTION [util].[uf_MergeDate2Time]( @date DATETIME, @time DATETIME ) RETURNS DATETIME WITH EXECUTE AS…


Windows Mobile Slingbox and SlingPlayer Mobile

So here is my 2nd favorite new gadget on my Windows Mobile device. With the 3G network speed and my SlingPlayer mobile, when I am not using Mobile Live search, I am either watching tv or listening to the news on my Windows Mobile Device. The quality is not great, but it is acceptable. I…


Live Search, Windows Mobile and my little GPS friend!

I have really started to like my Samsung Blackjack running Windows Mobile lately. I am going to start to share some of my favorite applications and gadgets as time permits. My latest and probably favorite as of now is Live Search for Windows Mobile. I am not going to run down all the features, but…


SQL Server Function to get the Hour of the Year

This function returns an integer of the hour of the year passed as a variable. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetHourOfYear]’) AND type IN (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) DROP FUNCTION [util].[uf_GetHourOfYear] GO CREATE FUNCTION [util].[uf_GetHourOfYear]( @date DATETIME ) RETURNS INTEGER WITH EXECUTE AS CALLER AS /********************************************************************************************************** * UDF Name: *…


SQL Server Function to get the Hour of the Month

This function returns an integer of the hour of the month passed as a variable. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetHourOfMonth]’) AND type IN (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) DROP FUNCTION [util].[uf_GetHourOfMonth] GO CREATE FUNCTION [util].[uf_GetHourOfMonth]( @date DATETIME ) RETURNS INTEGER WITH EXECUTE AS CALLER AS /********************************************************************************************************** * UDF Name: *…


SQL Server Function to return half year number of days.

This function returns an integer of the number of days in the half year.   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetHalfYearDays]’) AND type IN (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) DROP FUNCTION [util].[uf_GetHalfYearDays] GO CREATE FUNCTION [util].[uf_GetHalfYearDays]( @date DATETIME ) RETURNS INTEGER WITH EXECUTE AS CALLER AS /********************************************************************************************************** * UDF Name: *…


SQLCMD and the power of the little r

I would have to say that one of my favorite new utilities that shipped with SQL Server 2005 has been the SQLCMD utility.  I am going to demonstrate the use of include files. The following is the complete call syntax for SQLCMD. sqlcmd [{ { -U login_id [ -P password ] } | –E trusted…


SQL Server Data Types Test Tables

These tables can be used for testing various data types of SQL Server. You can extend as needed. SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,QUOTED_IDENTIFIER, ANSI_NULLS ON; GO   — Create XML Schema Collection CREATE XML SCHEMA COLLECTION PartitionMapSchema AS N'<xs:schema attributeFormDefault=”unqualified” elementFormDefault=”qualified” xmlns:xs=”http://www.w3.org/2001/XMLSchema”> <xs:element name=”Partition_Map”> <xs:complexType> <xs:sequence> <xs:element maxOccurs=”unbounded” name=”Value” type=”xs:date” /> </xs:sequence>…


Column Information View for SQL Server

Here is a view that contains column information. This is useful if you need to create a DDL statement for a table. CREATE VIEW dbo.vColumnInfo AS SELECT tbl.name AS [Table_Name], SCHEMA_NAME(tbl.schema_id) AS [Table_Schema], CAST(ISNULL(cik.index_column_id , 0) AS BIT) AS [InPrimaryKey], CAST(ISNULL(( SELECT TOP 1 1 FROM sys.foreign_key_columns AS colfk WHERE colfk.parent_column_id = clmns.column_id AND colfk.parent_object_id…