Archive for category SQL Server 2005
This post is a blatant link post about an issue I was having with SQL Server 2005 when writing T-SQL queries that needed to display dates based on the current connection’s language, in summary when writing queries always express explicit dates as ‘yyyy-mm-ddT00:00:00’ omitting the T or omitting the time will make the date language ambiguous. (I was using ‘yyyy-mm-dd’ thinking this was explicit)
Thanks to Jamie Thomson for posting this.
I find it useful to be able to truncate dates to a specific level, this is done like so:
SELECT DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0)
The above SQL statement gets the current UTC date rounded to the day, just change the two day intervals to minute or whatever to truncate to smaller values.
I was getting a little frustrated not being able to write a SQL query that used a Common Table Expression (CTE) with my INSERT INTO statement. I could run the select fine but then when I tried to add an INSERT INTO above that it did want to work… Then I realised that INSERT INTO is part of the main query and the WITH statement needs to come before all of it… Here’s an example:
WITH ActiveUsers AS
ROW_NUMBER() OVER (
Users.CreatedDate ASC) as UserRowNumber
(Users.IsDeleted = 0))
INSERT INTO [#ProcessUsers]
SELECT TOP (500) WITH TIES
UserRowNumber <= 100