-
MS SQL insert in the middle and reset auto increment
In case you need to insert something in the middle and need to set autoincremented column to some particular values, you can do the following: Set Identity_Insert [TableName] On -- Turn off identity insert for your Table ----------------------------------- Insert TableName (pkCol, [OtherColumns]) Values(pkValue, [OtherValues]) ----------------------------------- Set Identity_Insert [TableName] Off -- Turn off identity insert for… -
SQL query for today’s date minus year, month, day or minute
To add or subtract some date/time you can use MS SQL function: DATEADD(datepart, number, date) Let's say you need to add five months to current date, use this: SELECT * FROM YourTable WHERE YourDate < DATEADD(month, 5, GETDATE()) I used function GETDATE() for getting current DateTime. If you need to subtract some time, just add… -
How to select first row in each group ordered by some columns
I'm just gonna show you an example how to select first row in each group of logins ordered by IsActive and Created columns, hope you will realise how it works. For example i have a table: Id | Login | IsActive | Created 1 | test1 | 1 | 2016-01-01 2 | test1… -
Start of month from date in MS SQL (opposite to EOMONTH function)
Sometimes we need to select values grouped by month from MS SQL Table. Probably the easiest way to do it is to call function DateAdd with DateDiff: SELECT Sum(YourColumn), DATEADD(month, DATEDIFF(month, 0, [YourDateColumn]), 0) as 'Date' FROM YourTable GROUP BY DATEADD(month, DATEDIFF(month, 0, [YourDateColumn]), 0); Or we can use EOMONTH function that give us date that is…
Loading posts...