Uncategorized

First and last day of month using T-SQL

Just discovered the elegant way of getting the last day of the month in SQL Server 2012:

SELECT EOMONTH(CURRENT_TIMESTAMP)

Why is there not an equally elegant solution for the first day of the month? Here are four different ways. I like neither… :

SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
SELECT DATEADD(DD, -1 * DAY(GETDATE()) + 1, GETDATE())
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
SELECT DATEADD(DD, 1, EOMONTH(CURRENT_TIMESTAMP,-1))
SELECT GETDATE() - DAY(GETDATE()) + 1

SQL Server login with read access on every database

Here’s a quick script to add a new SQL server login with read access to every database on the server:

USE [master]
GO
 
CREATE LOGIN [AndreasReader] WITH PASSWORD=N'AndreasPasswordOhlalala12345'
GO
 
SELECT 
'USE ['+ name +']' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'EXEC sp_adduser [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 
'EXEC sp_addrolemember ''db_datareader'',''AndreasReader''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'EXEC sp_addrolemember ''db_denydatawriter'',''AndreasReader''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.databases

For newer versions of SQL Server, use this:

USE [master]
GO
 
CREATE LOGIN [AndreasReader] WITH PASSWORD=N'AndreasPasswordOhlalala12345'
GO
 
SELECT 
'USE ['+ name +']' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'CREATE USER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 
'ALTER ROLE [db_datareader] ADD MEMBER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'ALTER ROLE [db_denydatawriter] ADD MEMBER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.databases

Be sure to also add to the “model” database. That way you will also have read access to newly created databases.

Map of Runkeeper Tracks

After using different tracking apps for my runs and bike rides the last months, I wanted to see how it looked like when combined in the same map. After playing around with the TileMill application from mapbox.org I managed to make this neat little map (Pink=Biking, Yellow=Run/Hike):

I have merged the GPX-files from Runkeeper manually for now, but I will try to automate the process a bit more , and maybe store the results in my “Personal DWH”.