Thursday, June 7, 2012

Batch Update using SQL

Though this is a fairly easy process, its worth mentioning here since it is something which cannot be thought about. Using top x in your query, you could basically choose how many records need to be updated. This is helpful for processing the records as required
UPDATE top (100)  TableName 
SET
    column1 = getdate(),
    remarks = 'batch for API Upload'
 where column2 is not null and column1 > '2012-1-1'

Monday, June 4, 2012

Extract Date from DateTime Field in SQL

I was working on SQL to create a view to get the reports for our subscribers and group them by day. This is fairly easy to implement in .NET, but I have been licking SQL candies for a while and so wanted to try it out. This is how I did:
 SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
I could have used varchar converstion and "trimmed" the time value, but I had to further sort the resultset based upon the date obtained, so it was important for me to have it as a date type.