SQL Statements for First and Last Date of a Month and Week

by Bill Dowling 17. June 2013 11:26
The following tsql statements can be used to get the first and last dates in a month. DECLARE @InputDate DATE=GETDATE() SELECT DAY(DATEADD(MONTH,DATEDIFF(MONTH,-1,@InputDate),-1)) AS DaysInMonth SELECT DATEADD(M,DATEDIFF(M, 0, @InputDate), 0) AS FirstDate SELECT DATEADD(D,-1,DATEADD(M,DATEDIFF(M,0,@InputDate)+1,0)) AS LastDate SELECT DATEADD(S,-1,DATEADD(M,DATEDIFF(M,0,@InputDate)+1,0)) AS LastWithTime SELECT DateAdd(MONTH,DateDiff(MONTH, 0,GetDate())-1, 0) AS FirstDateLastMonth SELECT DateAdd(MONTH,DateDiff(MONTH,-1,GetDate())-1,-1) AS LastDateLastMonth SELECT CAST(GETDATE()-DATEPART(DW,GETDATE())+ 1 AS date) AS FirstDateOfWeek SELECT CAST(GETDATE()+(7 -DATEPART(DW,GETDATE())) AS date) AS LastDateOfWeek  SELECT DATEADD(MONTH,((FourDigitYear - 1900)* 12)+MonthNmbr - 1, 0) AS FirstDayInMonth  

Tags:

SQL Coding Examples

ufs_URLSite Scalar-Valued Function

by Bill Dowling 29. July 2010 08:22
Returns Web Site name from a URL line. USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufs_URLSite] ( @URL varchar(2000) ) RETURNS varchar(255) AS BEGIN DECLARE @Result varchar(2000), @SearchFirst varchar(100), @SearchNext varchar(100), @Pos int SET @Result = @URL SET @SearchNext = '/' -- ************** -- Check for http -- ************** SET @SearchFirst = 'http://' SET @Pos = CHARINDEX(@SearchFirst, @Result, 1) -- ********************* -- Nope, check for https -- ********************* IF @Pos = 0 BEGIN SET @SearchFirst = 'https://' SET @Pos = CHARINDEX(@SearchFirst, @Result, 1) END -- ******************* -- Nope, check for www -- ******************* IF @Pos = 0 BEGIN SET @SearchFirst = 'www.' SET @Pos = CHARINDEX(@SearchFirst, @Result, 1) -- ****************************************** -- IF www then fudge with http in front of it -- ****************************************** IF @Pos = 1 BEGIN SET @Result = 'http://' + @Result SET @SearchFirst = 'http://' + @SearchFirst END END -- *************** -- Found something -- *************** IF @Pos = 1 BEGIN SET @Pos = CHARINDEX(@SearchNext, @Result, LEN(@SearchFirst) + @Pos) IF @Pos > 1 -- save up to next slash SET @Result = SUBSTRING(@Result, 1, @Pos - 1) -- ****************** -- Do we have a query -- ****************** SET @Pos = CHARINDEX('?', @Result, LEN(@SearchFirst) + 1) IF @Pos > 1 -- remove the query SET @Result = SUBSTRING(@Result, 1, @Pos - 1) -- ************************ -- Do we have a port number -- ************************ SET @Pos = CHARINDEX(':', @Result, LEN(@SearchFirst) + 1) IF @Pos > 1 -- remove the port SET @Result = SUBSTRING(@Result, 1, @Pos - 1) END -- ************* -- Special Cases -- ************* IF CHARINDEX('http://servername', @Result) > 0 SET @Result = 'http://www.mycompany.com' -- ******************************************************** -- We want to limit the URL LENgth to a specific max LENgth -- ******************************************************** IF LEN(@Result) > 255 SET @Result = SUBSTRING(@Result, 1, 255) RETURN @Result END

Tags:

SQL Coding Examples

ufs_URLEncode Scalar-Valued Function

by Bill Dowling 29. July 2010 08:14
Returns encoded URL line. For example, all spaces are replaced with %20. USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufs_URLEncode](@url varchar(1024)) RETURNS varchar(3072) AS BEGIN -- Peter DeBetta's SQL Programming Blog -- http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urlencode.ASpx DECLARE @count int, @c char(1), @i int, @urlReturn varchar(3072) SET @count = LEN(@url) SET @i = 1 SET @urlReturn = '' WHILE (@i <= @count) BEGIN SET @c = SUBSTRING(@url, @i, 1) --IF @c LIKE '[A-Za-z0-9()''*-._! ]' IF @c LIKE '[A-Za-z0-9''*-._! ]' BEGIN SET @urlReturn = @urlReturn + @c End Else BEGIN SET @urlReturn = @urlReturn + '%' + SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS varbinary(max))), 3, 2) End SET @i = @i +1 End RETURN @urlReturn END

Tags:

SQL Coding Examples

ufs_URLDecode Scalar-Valued Function

by Bill Dowling 29. July 2010 08:10
Returns decoded URL line. For example, %20 is replaced with a space. USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE Function [dbo].[ufs_URLDecode](@url varchar(3072)) RETURNS varchar(3072) AS BEGIN -- Peter DeBetta's SQL Programming Blog -- http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urldecode.aspx DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(3072) SET @count = LEN(@url) SET @i = 1 SET @urlReturn = '' WHILE (@i <= @count) BEGIN SET @c = SUBSTRING(@url, @i, 1) IF @c LIKE '[!%]' ESCAPE '!' BEGIN SET @cenc = SUBSTRING(@url, @i + 1, 2) SET @c = CHAR(CASE WHEN SUBSTRING(@cenc, 1, 1) LIKE '[0-9]' THEN CAST(SUBSTRING(@cenc, 1, 1) AS int) ELSE CAST(ASCII(Upper(SUBSTRING(@cenc, 1, 1)))-55 AS int) END * 16 + CASE WHEN SUBSTRING(@cenc, 2, 1) LIKE '[0-9]' THEN CAST(SUBSTRING(@cenc, 2, 1) AS int) ELSE CAST(ASCII(Upper(SUBSTRING(@cenc, 2, 1)))-55 AS int) END) SET @urlReturn = @urlReturn + @c SET @i = @i + 2 END ELSE BEGIN SET @urlReturn = @urlReturn + @c END SET @i = @i +1 END RETURN @urlReturn END

Tags:

SQL Coding Examples

uft_ReadFileAsTable Table-Valued Function

by Bill Dowling 28. July 2010 07:17
Returns a table with each line from the text file written to a row in the table. USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[uft_ReadFileAsTable] ( @Path VARCHAR(255), -- Folder containing file @Filename VARCHAR(100), -- FileName to read @MaxLines INT = 0, -- Default Maximum number of lines to read @FindLine VARCHAR(100) = NULL -- Find First Occurance of line beginning with this ) RETURNS @File TABLE ( [LineNum] INT IDENTITY (1,1), Line VARCHAR(8000) ) AS BEGIN DECLARE @objFileSystem INT, @objTextStream INT, @objErrorObject INT, @strErrorMessage VARCHAR(1000), @Cmd VARCHAR(1000), @hr INT, @String VARCHAR(8000), @YesOrNo INT, @CountThem int Set @CountThem = 1 If @MaxLines = 0 Set @CountThem = 0 Select @strErrorMessage = 'opening the File System Object' EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUTPUT if @hr = 0 Begin Select @objErrorObject = @objFileSystem Select @strErrorMessage = 'Opening file "' + @path + '\' + @filename + '"' Select @Cmd = @path + '\' + @filename EXECUTE @hr = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUTPUT, @Cmd, 1, false, 0 --for reading, FormatASCII End WHILE @hr = 0 Begin If @CountThem = 1 Begin Select @MaxLines = @MaxLines - 1 If @MaxLines < 0 Break End Select @objErrorObject = @objTextStream Select @strErrorMessage = 'finding out if there is more to read in "' + @filename + '"' EXECUTE @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT IF @YesOrNo <> 0 break if @hr = 0 Begin Select @objErrorObject = @objTextStream Select @strErrorMessage = 'reading from the output file "' + @filename + '"' EXECUTE @hr = sp_OAMethod @objTextStream, 'Readline', @String OUTPUT If (@FindLine Is NULL) Or (@FindLine Is Not NULL And @FindLine = SUBSTRING(@String, 1, LEN(@FindLine))) Begin Insert Into @file(line) Select @String if @FindLine Is Not NULL break End End End if @hr = 0 Begin Select @objErrorObject = @objTextStream Select @strErrorMessage = 'closing the output file "' + @filename + '"' EXECUTE @hr = sp_OAMethod @objTextStream, 'Close' End if @hr <> 0 Begin Declare @Source varchar(255), @Description Varchar(255), @Helpfile Varchar(255), @HelpID int EXECUTE sp_OAGetErrorInfo @objErrorObject, @source output,@Description output,@Helpfile output,@HelpID output Select @strErrorMessage = 'Error whilst ' + Coalesce(@strErrorMessage , 'doing something') + ', ' + Coalesce(@Description, '') Insert Into @File(line) Select @strErrorMessage End EXECUTE sp_OADestroy @objTextStream -- Fill the table variable with the rows for your result set RETURN END

Tags:

SQL Coding Examples

uft_ParseString Table-Valued Function

by Bill Dowling 28. July 2010 07:13
Returns a table with parsed elements (words) in a passed string. USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[uft_ParseString] ( @linetoparse VARCHAR(2000), @delimeter CHAR(1) ) RETURNS @elements TABLE ( ElementID INT IDENTITY (1,1), Element VARCHAR(2000) ) AS Begin Declare @LineIn VARCHAR(2000), @Element VARCHAR(2000), @StartPos INT, @Length INT Set @LineIn = @linetoparse While LEN(@LineIn) > 0 Begin Set @StartPos = CHARINDEX(@delimeter, @LineIn) If @StartPos < 0 Set @StartPos = 0 Set @Length = LEN(@LineIn) - @StartPos - 1 If @Length < 0 Set @Length = 0 If @StartPos > 0 Begin Set @Element = SUBSTRING(@LineIn, 1, @StartPos - 1) Set @LineIn = SUBSTRING(@LineIn, @StartPos + 1, LEN(@LineIn) - @StartPos) End Else Begin Set @Element = @LineIn Set @LineIn = '' End Insert Into @elements(Element) Select @Element End Return End

Tags:

SQL Coding Examples

usp_DirFolder Stored Procedure

by Bill Dowling 28. July 2010 06:58
Returns the filenames contained in the passed Folder name. USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_DirFolder] @PCWrite varchar(2000), -- The first parameter is a path to a directory @DBTable varchar(100)= NULL, -- The second parameter is a table name in which to insert the file names @PCIntra varchar(100)= NULL, -- The third parameter is a filter for including certain names @PCExtra varchar(100)= NULL -- The fourth parameter is a filter for excluding certain names AS SET NOCOUNT ON DECLARE @Return int, @Retain int, @Status int SET @Status = 0 DECLARE @Task varchar(2000), @Work varchar(2000), @Wish varchar(2000) /* ##### Example ##### Create Table #TempDir (FileName varchar(200)) EXECUTE spDirFolder '\\Webserver\logfiles\W3SVC1', #TempDir Select * From #TempDir Order By FileName Drop Table #TempDir */ SET @Work = 'DIR ' + '"' + @PCWrite + '"' CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1)) INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain IF @Status = 0 SET @Status = @Return IF (SELECT COUNT(*) FROM #DBAZ) < 4 BEGIN SELECT @Wish = Name FROM #DBAZ WHERE Work = 1 IF @Wish IS NULL BEGIN RAISERROR ('General error [%d]',16,1,@Status) END ELSE BEGIN RAISERROR (@Wish,16,1) END END ELSE BEGIN DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING(Name,40,1) = '.' OR Name LIKE '%.lnk' IF @DBTable IS NULL BEGIN SELECT SUBSTRING(Name,40,100) AS FileName FROM #DBAZ WHERE 0 = 0 And (Name NOT LIKE '%<DIR>%') AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra) AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra) ORDER BY 1 END ELSE BEGIN SET @Task = ' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95)) -- 32 (space) 95 (_) + ' SELECT SUBSTRING(Name,40,100) AS FileName' + ' FROM #DBAZ' + ' WHERE 0 = 0' + ' And (Name NOT LIKE ''%<DIR>%'')' + CASE WHEN @PCIntra IS NULL THEN '' ELSE ' AND SUBSTRING (Name,40,100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39) END + CASE WHEN @PCExtra IS NULL THEN '' ELSE ' AND SUBSTRING (Name,40,100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39) END + ' ORDER BY 1' IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return END END DROP TABLE #DBAZ SET NOCOUNT OFF RETURN (@Status)

Tags:

SQL Coding Examples

SQLdbCopy Tech Info

by Bill Dowling 28. July 2010 06:19
Different SQL Server statements need to be performed depending on what version of SQL Server you are running. Below are some of the statements used in SQLdbCopy to perform specific tasks, both for SQL Server and Visual Basic with Visual Studio 2005 and 2010.   Sql Server 2000 Version 8 Sql Server 2005 Version 9 Sql Server 2008 Version 10   OleDB Provider statement for Access Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};  [{0} = Full Filename] .Net Date/Time Formatting MM/dd/yyyy HH:mm:ss .Net List of SQL Servers Dim dt As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources 2000 List Databases SELECT * FROM dbo.sysdatabases; 2005 List Databases SELECT * FROM sys.sysdatabases; 2000 List Tables SELECT u.name AS SchemaName, o.name AS TableName , SUM(CASE WHEN (c.status & 0x80 = 0x80) THEN 1 ELSE 0 END) AS SetIdentity FROM sysobjects AS o INNER JOIN sysusers AS u ON o.uid = u.uid INNER JOIN syscolumns AS c ON o.id = c.id WHERE (o.xtype = 'U') AND (o.name NOT LIKE 'dtproperties') AND (o.name NOT LIKE 'tmp%') AND (o.name NOT LIKE 'sys%') GROUP BY u.name, o.name ORDER BY u.name, o.name; 2005 List Tables SELECT s.name AS SchemaName, t.name AS TableName, SUM(CASE WHEN (c.is_identity = 1) THEN 1 ELSE 0 END) AS SetIdentity FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.columns AS c on t.object_id = c.object_id WHERE (t.type = 'U') AND (t.name NOT LIKE 'dtproperties') AND (t.name NOT LIKE 'tmp%') AND (t.name NOT LIKE 'sys%') GROUP BY s.name, t.name ORDER BY s.name, t.name;td> 2000 List Primary Key SELECT u.name AS SchemaName, t.name AS TableName, ix.name AS IndexName, ik.keyno AS ColumnNum, c.name AS ColumnName FROM dbo.sysobjects AS t INNER JOIN dbo.sysindexes AS ix ON t.id = ix.id INNER JOIN dbo.sysindexkeys AS ik ON ix.id = ik.id AND ix.indid = ik.indid INNER JOIN dbo.syscolumns AS c ON ik.id = c.id AND ik.colid = c.colid INNER JOIN dbo.sysusers AS u ON t.uid = u.uid WHERE ((ix.status & 2048) <> 0) AND (u.name = '{0}') AND (t.name = '{1}') ORDER BY t.name, ix.name, ik.keyno;  [{0}=SchemaName, {1}=TableName] 2005 List Primary Key SELECT s.name AS SchemaName, t.name AS TableName, ix.name AS IndexName, ic.index_column_id AS ColumnNum, c.name AS ColumnName FROM sys.tables AS t INNER JOIN sys.indexes AS ix ON t.object_id = ix.object_id INNER JOIN sys.index_columns AS ic ON ix.object_id = ic.object_id AND ix.index_id = ic.index_id INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id WHERE (ix.is_primary_key = 1) AND (s.name = '{0}') AND (t.name = '{1}') ORDER BY t.name, ix.name, ic.index_column_id;  [{0}=SchemaName, {1}=TableName] 2000 List Columns SELECT u.name AS SchemaName, t.name AS TableName, c.colid AS ColumnID, c.name AS ColumnName, dt.name AS DataType, c.length AS MaxLength, c.prec AS ColumnPrec, c.scale AS ColumnScale FROM dbo.syscolumns AS c INNER JOIN dbo.sysobjects AS t ON c.id = t.id INNER JOIN dbo.systypes AS dt On c.xusertype = dt.xusertype INNER JOIN dbo.sysusers AS u ON t.uid = u.uid WHERE (u.name = '{0}') AND (t.name = '{1}') AND (dt.name NOT LIKE 'sys%') ORDER BY t.name, c.colid;  [{0}=SchemaName, {1}=TableName] 2005 List Columns SELECT s.name AS SchemaName, t.name AS TableName, c.column_id AS ColumnID, c.name AS ColumnName, dt.name AS DataType, c.max_length AS MaxLength, c.precision AS ColumnPrec, c.scale AS ColumnScale FROM sys.tables AS t INNER JOIN sys.columns AS c ON t.object_id = c.object_id INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.types AS dt ON c.system_type_id = dt.user_type_id WHERE (s.name = '{0}') AND (t.name = '{1}') AND (dt.name NOT LIKE 'sys%') ORDER BY t.name, c.column_id;  [{0}=SchemaName, {1}=TableName] We have added a SetIdentity field in the "List Tables" code to identify tables that have a column with "IDENTITY" set. This allows us to identify which tables need "SET IDENTITY_INSERT [schemaname].[tablename] On/Off" statements when inserting data. For Access Database output, we currently process the following SQL data types and what ADO format we convert them to. If a character (adVarWChar) based field is larger then 255 characters, it will be converted to an Access Memo (adLongVarWChar) field.   SQL TypeDataTypeEnumProcedure TypeOleDbType char adVarWChar TEXT VarWChar nchar adVarWChar TEXT VarWChar text adLongVarWChar MEMO LongVarWChar ntext adLongVarWChar MEMO LongVarWChar varchar adVarWChar TEXT VarWChar nvarchar adVarWChar TEXT VarWChar bigint adNumeric NUMERIC Numeric int adInteger LONG Integer smallint adSmallInt INTEGER SmallInt tinyint adUnsignedTinyInt NUMERIC UnsignedTinyInt bit adBoolean BIT Boolean decimal adNumeric NUMERIC Numeric numeric adNumeric NUMERIC Numeric money adCurrency CURRENCY Currency smallmoney adCurrency CURRENCY Currency float adDouble DOUBLE Double real adDouble DOUBLE Double date adDate DATE Date datetime adDate DATE Date smalldatetime adDate DATE Date binary adLongVarBinary BINARY LongVarBinary varbinary adLongVarBinary LONGBINARY LongVarBinary image adLongVarBinary LONGBINARY LongVarBinary timestamp adVarBinary BINARY VarBinary uniqueidentifier adGUID GUID Guid cursor not translated sql_variant not translated table not translated xml adLongVarWChar MEMO LongVarWChar

Tags: , ,

Access DB | SQL Coding Examples

About the author

Bill Dowling has been a VB and .Net programmer for as long as he can remember.

Month List