Query to Find First and Last Day of Previous, Current and Next Month - SQL Server

Hello All,

This is normal requirement to find First and Last day of Month. I got one in which I have to find not only current month or given month but Previous and Next month as well.

Following query will gives start date and end date for previous, current and next month respectively. That is First Day of Previous Month, Last Day of Previous Month, First Day of Current Month, Last Day of Current Month, First Day of Next Month and Last Day of Next Month.

DECLARE @PreviousMonthFirstDay DATETIME
DECLARE @PreviousMonthLastDay DATETIME
DECLARE @CurrentMonthFirstDay DATETIME
DECLARE @CurrentMonthLastDay DATETIME
DECLARE @NextMonthFirstDay DATETIME
DECLARE @NextMonthLastDay DATETIME

DECLARE @CurrentDate AS DATETIME = CONVERT( DATETIME, CONVERT( DATE, GetDate() ) )

SELECT @CurrentMonthFirstDay = DATEADD(dd, -(DAY(@CurrentDAte)) + 1, @CurrentDate)
SELECT @CurrentMonthLastDay = DATEADD(d, -1, DATEADD(mm, 1, @CurrentMonthFirstDay))

SELECT @PreviousMonthFirstDay = DATEADD(m, -1, @CurrentMonthFirstDay)
SELECT @PreviousMonthLastDay = DATEADD(d, -1, @CurrentMonthFirstDay)

SELECT @NextMonthFirstDay = DATEADD(d, 1, @CurrentMonthLastDay)
SELECT @NextMonthLastDay = DATEADD(d, -1, DATEADD(mm, 1, @NextMonthFirstDay))

SELECT 
     @PreviousMonthFirstDay PreviousMonthFirstDay
    ,@PreviousMonthLastDay PreviousMonthLastDay
    ,@CurrentMonthFirstDay CurrentMonthFirstDay
    ,@CurrentMonthLastDay CurrentMonthLastDay
    ,@NextMonthFirstDay NextMonthFirstDay
    ,@NextMonthLastDay NextMonthLastDay 

Output as of 12-Dec-2012
You can make this more generic by replacing GetDate() with perticular date while setting up value of @CurrentDate

Operation is not valid due to the current state of the object

Hello All,

While working with web application, there may be case we got ‘Operation is not valid due to the current state of the object error’ or ‘The URL-encoded form data is not valid’ while doing postback

There may be other reason but one of the reason is form collection reaches its limit of maximum number of keys that it can hold a time. Means you have huge amount of data and you are trying to postback that data to server which end up with this error

‘Operation is not valid due to the current state of the object error’ or ‘The URL-encoded form data is not valid’

Microsoft has added this as security for Form can have 1000 items at a time, however thanks Microsoft also provides way to change this

To change this add following setting into your configuration file

<appSettings>
  <add key="aspnet:MaxHttpCollectionKeys" value="10000" />    
</appSettings>

In case you have issue with Jason data then you have to add following settings

<appSettings>
  <add key="aspnet:MaxJsonDeserializerMembers" value="10000" /></appSettings>

Hope this helps

Deleting Parent and its child record without setting ON CASCADE DELETE

Hello All,

Very recent I found requirement for removing parent record which is having tons of relation as they have foreign key constrains; you can’t delete them unless you have ON CASCADE DELETE

I found solution on Stack Overflow and base main article from sqlteam. I have modified procedure to fix some minor issue and here is the procedure which take table name and a query to filter row which we need to delete from table along with its reference.

-- ================================================
-- Expects the name of a table, and a conditional for selecting rows
-- within that table that you want deleted.
-- Produces SQL that, when run, deletes all table rows referencing the ones
-- you initially selected, cascading into any number of tables,
-- without the need for "ON DELETE CASCADE".
-- Does not appear to work with self-referencing tables, but it will
-- delete everything beneath them.
-- To make it easy on the server, put a "GO" statement between each line.
-- ================================================
CREATE PROCEDURE DeleteCascade (
     @BaseTableName VARCHAR(200)
    ,@BaseCriteria VARCHAR(1000)
)
AS BEGIN

    DECLARE @ToDelete TABLE 
    (
        Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
        Criteria VARCHAR(5000) NOT NULL,
        TableName VARCHAR(200) NOT NULL,
        Processed BIT NOT NULL,
        DeleteSql VARCHAR(5000)
    )

    SET NOCOUNT ON

    INSERT INTO @ToDelete (Criteria,TableName , Processed)
    VALUES (@BaseCriteria, @BaseTableName, 0)

    DECLARE @Id INT
    DECLARE @Criteria VARCHAR(5000)
    DECLARE @TableName VARCHAR(5000)

    WHILE EXISTS(SELECT 1 FROM @ToDelete WHERE Processed = 0) 
    BEGIN
    
        SELECT TOP 1 
            @Id = Id
            ,@Criteria = Criteria
            ,@TableName = TableName 
        FROM @ToDelete 
        WHERE Processed = 0 
        ORDER BY Id DESC

        INSERT INTO @ToDelete (Criteria, TableName, Processed)
        SELECT 
            ReferencingColumn.name + ' IN (SELECT [' + ReferencedColumn.name + '] FROM [' 
+ @TableName +'] WHERE ' + @Criteria + ')',
            ReferencingTable.name,0
        FROM sys.foreign_key_columns fk
        INNER JOIN sys.columns ReferencingColumn 
            ON fk.parent_object_id = ReferencingColumn.object_id 
            AND fk.parent_column_id = ReferencingColumn.column_id 
        INNER JOIN sys.columns ReferencedColumn 
            ON fk.referenced_object_id = ReferencedColumn.object_id 
            AND fk.referenced_column_id = ReferencedColumn.column_id 
        INNER JOIN sys.objects ReferencingTable 
            ON fk.parent_object_id = ReferencingTable.object_id 
        INNER JOIN sys.objects ReferencedTable 
            ON fk.referenced_object_id = ReferencedTable.object_id 
        INNER JOIN sys.objects constraint_object 
            ON fk.constraint_object_id = constraint_object.object_id
        WHERE ReferencedTable.name = @TableName
        AND ReferencingTable.name != ReferencedTable.name

        UPDATE @ToDelete 
        SET Processed = 1
        WHERE Id = @Id
        
    END
    
    SELECT 
        'PRINT ''Deleting from ' + TableName + '...''; DELETE FROM [' + TableName + '] WHERE ' 
+ Criteria 
    FROM @ToDelete 
    ORDER BY Id DESC
    
END

When you execute this, you will see list of DELETE statement, which you need to run.

Enumeration Types as Bit Flags

An enum is best practice to give internal constants to readable string format, example

It’s very handy to use string rather to remember int value and it also helps to maintain code if down the road we need to change integer value it wont be difficult. I am sure developer knows very well about how to use enum in their code.

The post is all about answering few question comes while development or requirement, How you pass multiple value by using enum? or How to assign multiple enum values to variable? or How can I store combination of enum values using single enum variable?

All question have single answer, to use enum as bit flags. In order to achieve this, you have to add new attribute Flags to your enum declaration. As these values are bit you can use bitwise operation which are AND, OR, XOR and NOT.

Let’s see useful example of Flag enum using normal coding and then we will see same thing using Flags.

In normal coding if I want to calculate discount for four country and more then one at same time, I rather create for methods and call four individual functions

OR I can add bool into signature for calculating particular discount.

Drawback of first method is you have to keep adding new functions if you introduce new country and in second you have to pass false to country which you don’t want to do calculation, so signature will get increase in case we have more countries

Now lets use Flag enumeration to avoid mention drawbacks.

This will how your new enum will look like

A new attribute called Flags is added and we have assigned constants in power or TWO, that is 1,2,4,8 and so on, so when we use in combination no one get overlap.

Lets go ahead and create CalculateDiscount function and use into our program.

So single function CalculateDiscount is responsible to calculate discount for four different country at a time. If I want to calculate discount for German and Canada, you can do that by passing both in single argument using ‘|’ operator, its bitwise OR operator. You can use ‘&’ bitwise AND operator to determine whether a specific flag is set or not.

Changing Physical Path of multiple Applications in IIS 7

Hello All,

Usually, we need functionality where we have more then one set of application configured in our machine, like QA environment and DEV environment. We don’t have way to keep two directory pointing to same location in single web site.

We can use backup feature for IIS 7 which helps to take backup as well as change directories of multiple applications at same time. First we are going to do backup IIS7

Go to your inetsvr directory for your system’s system32 directory from command prompt. Make sure you are using Administrator account, if not then open command prompt with “run as administrator”

 

Write following command in command prompt

appcmd add backup NAME_OF_BACKUP

I have created backup with name IISBackup, So we created backup of IIS including application pool, application, virtual directory etc. We will now change the physical path of more then one application and then restore backup back to IIS

Open backup folder in explorer.

Open that folder and edit applicationHost.config into notepad

In sites tag you can see your application with all details. Update physicalPath with your new path and save the file

Once you are done with this, go back to command prompt to restore our updated IIS settings, write following command, it will take few secs to set your new configurations.

appcmd restore backup NAME_OF_BACKUP

You are done!

So, we see how to backup and restore IIS, and how to change physical path of more then one applications.