Tag Archives: tsql

How to convert UTC to local time (in the GMT time zone) taking into account daylight savings.

May 1, 2015Reporting, sql, SQL Server, T-SQLConvert UTC, convert utc to local time, Convert UTC to local time (in GMT time zone) taking into account daylight savings., database, daylight savings, function, guide, How To, howto, in GTM time, ireland, lesson, local time, SQL Server, tsql, tutorial, uncategorised, utc daylight savingsTidbytez

It’s common practice to set a database to UTC time. Coordinated Universal Time, abbreviated as UTC, is the primary time standard by which the world regulates clocks and time. For most purposes, UTC is considered interchangeable with GMT, but GMT is no longer precisely defined by the scientific community. If you don’t believe me then I refer you to the following link.

This creates problems however as UTC does not observe daylight saving time. So if you were to prepare a report regarding a company’s call data calls may show as having occurred at seven in the morning before offices had opened. When daylight savings is taken into account the calls actually occurred at eight.

SQL Server has no inbuilt conversion process to deal with this but a simple function can be created to deal with this as below.

My example deals with Ireland specifically. As countries differ in when they implement daylight savings if the company is not based in Ireland you will have to adjust the logic below to suit your needs.

I would recommend searching online and creating a table containing previous and future year values of daylight savings times and then plugging these values into the script below.

	IF OBJECT_ID('[dbo].[UTCToLocalTime]') IS NOT NULL
	DROP FUNCTION [dbo].[UTCToLocalTime];
	GO

	CREATE FUNCTION [dbo].[UTCToLocalTime] (@UTC AS DATETIME)
	RETURNS DATETIME
	AS
	BEGIN
	DECLARE @Hour AS TINYINT

	SET @Hour = 1
	–Regardless of year day light savings
	–is applied between April and September
	IF (
	MONTH(@UTC) > 3
	AND MONTH(@UTC) < 10
	)
	BEGIN
	SET @UTC = DATEADD(HOUR, + @Hour, @UTC)
	END
	–Regardless of year day light savings
	–is not applied between November and February
	ELSE IF (
	MONTH(@UTC) > 10
	AND MONTH(@UTC) < 3
	)
	BEGIN
	SET @UTC = @UTC
	END
	–If date variable is during March or October
	–assess if daylight savings is applied dependent on year
	ELSE IF (
	@UTC BETWEEN '2009-03-29 01:00:00'
	AND '2009-10-25 02:00:00'
	)
	OR (
	@UTC BETWEEN '2010-03-28 01:00:00'
	AND '2010-10-31 02:00:00'
	)
	OR (
	@UTC BETWEEN '2011-03-27 01:00:00'
	AND '2011-10-30 02:00:00'
	)
	OR (
	@UTC BETWEEN '2012-03-25 01:00:00'
	AND '2012-10-28 02:00:00'
	)
	OR (
	@UTC BETWEEN '2013-03-31 01:00:00'
	AND '2013-10-27 02:00:00'
	)
	OR (
	@UTC BETWEEN '2014-03-30 01:00:00'
	AND '2014-10-26 02:00:00'
	)
	OR (
	@UTC BETWEEN '2015-03-29 01:00:00'
	AND '2015-10-25 02:00:00'
	)
	OR (
	@UTC BETWEEN '2016-03-27 01:00:00'
	AND '2016-10-30 02:00:00'
	)
	OR (
	@UTC BETWEEN '2017-03-26 01:00:00'
	AND '2017-10-29 02:00:00'
	)
	OR (
	@UTC BETWEEN '2018-03-25 01:00:00'
	AND '2018-10-28 02:00:00'
	)
	OR (
	@UTC BETWEEN '2019-03-31 01:00:00'
	AND '2019-10-27 02:00:00'
	)
	BEGIN
	SET @UTC = DATEADD(HOUR, + @Hour, @UTC)
	END
	ELSE
	BEGIN
	SET @UTC = @UTC
	END

	RETURN @UTC
	END

view raw

UTCToLocalTime

hosted with ❤ by GitHub

Distinct Row Count Table

April 9, 2015T-SQLCount, Distinct, Distinct Row Count Table, LOOP, Row, Table, tsqlTidbytez

If you come from a programming background you might want to do a loop using TSQL rather than using a cursor.

The syntax for this is pretty recognisable, exempli gratia,

	DECLARE @thisThing INT
	DECLARE @thatThing INT

	SET @thisThing = 1
	SET @thatThing = 10

	WHILE @thisThing < @thatThing
	BEGIN
	–Do stuff
	SELECT GETDATE()
	SET @thisThing = @thisThing + 1
	END

view raw

gistfile1.sql

hosted with ❤ by GitHub

However if you want to feed in dynamic variables captured from a table you can create a distinctive row count table.

This deduplicates the occurrence of a field value and assigns it a row number.

Where this can be used will be included in a more complicated example coming in the future.

	IF OBJECT_ID('tempdb..#rowCountTB') IS NOT NULL
	DROP TABLE #rowCountTB;

	WITH DistinctTable
	AS (
	SELECT DISTINCT [fieldValue]
	FROM [tableName]
	)
	,RowNumber
	AS (
	SELECT ROW_NUMBER() OVER (
	ORDER BY [fieldValue]
	) AS RowNum
	,[fieldValue]
	FROM DistinctTable
	)
	SELECT *
	INTO #rowCountTB
	FROM RowNumber
	ORDER BY [fieldValue]

view raw

gistfile1.sql

hosted with ❤ by GitHub

How to dynamically pull all text file names from a folder into a SQL Server table

March 10, 2015T-SQLdirectory, Dynamically pull all text file names from a folder into a SQL Server table, file names, folder, guide, How To, howto, lesson, T-SQL, table of file names, text files, tsql, tutorial, uncategorisedTidbytez

So here’s a quick out of the box solution for . . . you guessed it, dynamically pulling all text files from a folder into a SQL Server table. On it’s own this script isn’t very powerful but it can be paired with SQL queries to make some powerful functionality.

To use simply change the @path variable to the directory path you’re targeting and as it states in the comments . . .

-- NOTE: MAKE SURE THE BACKSLASH IS INCLUDED AT THE END OF THE STRING
-- *.txt IS REFERENCED IN THIS EXAMPLE BUT *.xls COULD ALSO BE USED

Sayōnara

	— THIS QUERY USES CMD TO CAPTURE ALL THE FILES IN A FOLDER
	— AND WRITE THE FILE NAMES TO A TABLE
	— THE QUERY REQUIRES THAT xp_cmdShell BE ENABLED TO WORK
	— THE FOLLOWING ENABLES xp_cmdShell:
	— TO ALLOW ADVANCED OPTIONS TO BE CHANGED
	EXEC sp_configure 'show advanced options'
	,1
	GO

	— TO UPDATE THE CURRENTLY CONFIGURED VALUE FOR ADVANCED OPTIONS
	RECONFIGURE
	GO

	— TO ENABLE THE FEATURE
	EXEC sp_configure 'xp_cmdshell'
	,1
	GO

	— TO UPDATE THE CURRENTLY CONFIGURED VALUE FOR THIS FEATURE
	RECONFIGURE
	GO

	— THE FOLLOWING CREATES A TABLE AND POPULATES IT WITH THE FILES LOCATED IN THE SPECIFIED DIRECTORY
	IF OBJECT_ID('ALLFILENAMES', 'U') IS NOT NULL
	DROP TABLE ALLFILENAMES

	CREATE TABLE ALLFILENAMES (
	WHICHPATH VARCHAR(255)
	,WHICHFILE VARCHAR(255)
	)

	DECLARE @filename VARCHAR(255)
	,@path VARCHAR(255)
	,@cmd VARCHAR(1000)

	— GET THE LIST OF FILES TO PROCESS
	— NOTES: MAKE SURE THE BACKSLASH IS INCLUDED AT THE END OF THE STRING
	— .txt IS REFERENCED IN THIS EXAMPLE BUT .xls COULD ALSO BE USED
	SET @path = 'C:\Users\Data\'
	SET @cmd = 'dir ' + @path + '*.txt /b'

	INSERT INTO ALLFILENAMES (WHICHFILE)
	EXEC Master..xp_cmdShell @cmd

	UPDATE ALLFILENAMES
	SET WHICHPATH = @path
	WHERE WHICHPATH IS NULL

	SELECT *
	FROM ALLFILENAMES

view raw

DynamicallyPullTextFileNames

hosted with ❤ by GitHub

Share this:

Share this:

Share this: