Archive for category SQL Server 2005

SQL Server Date Processing

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.

Truncating Dates

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.

Advertisements

Leave a comment

Using a Common Table Expression (CTE) with an INSERT INTO statement

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
(
   
SELECT
       
Users.UserName,
       
Users.TimezoneID,
       
ROW_NUMBER() OVER (
           
PARTITION BY
               
Users.TimezoneID
            ORDER BY
               
Users.CreatedDate ASC) as UserRowNumber
    FROM
       
Users
    WHERE
       
(Users.IsDeleted = 0))
)   
INSERT INTO [#ProcessUsers]
    (UserID, TimezoneID)
   
SELECT TOP (500) WITH TIES
       
UserName
    FROM
       
ActiveUsers
    WHERE
       
UserRowNumber <= 100
    ORDER BY
       
UserID;

5 Comments