Friday, May 13, 2016

SSRS 2005 - How To Format Globals!ExecutionTime To "MM/dd/yyyy"


To format the build-in global in SSRS 2005 you have to you  FORMAT functions which will take two parameters (Expression & Style)


Example

         = FORMAT(Globals!ExecutionTime, "MM/dd/yyyy") 


Friday, August 14, 2015

Select Query - Case Statement In Order by - Msg 241 - Conversion failed when converting datetime from character string Error

        Today when i was working on store procedure which i need to use case statement in Order by in the select clause i have came across the following issue,

Msg 241, Level 16, State 1, Line 16
Conversion failed when converting datetime from character string.


        After a spending some time I found the root cause of this issue, the golden rule we have to use the same type in all the branches of case/when.

Sample Code

Execute the below sample script to recreate that issue,

DECLARE @tmpTBL table
(
    ID INT IDENTITY(1,1),
    SampleDT datetime
)

INSERT INTO @tmpTBL VALUES('2015-08-01');
INSERT INTO @tmpTBL VALUES('2015-08-02');
INSERT INTO @tmpTBL VALUES('2015-08-03');
INSERT INTO @tmpTBL VALUES('2015-08-04');

Declare @SordDirection CHAR(1); SET @SordDirection = 'D';
Declare @OrderBy VARCHAR(10);SET @OrderBy = 'SampleDT';

SELECT * FROM @tmpTBL
ORDER BY
        CASE WHEN @SordDirection ='D' THEN 'A'
        ELSE
        CASE WHEN @OrderBy = 'SampleDT' THEN SampleDT
        END
        END ASC,
        CASE WHEN @SordDirection='A' THEN 'D'
        ELSE
        CASE WHEN @OrderBy = 'SampleDT' THEN SampleDT
        END
        END DESC












 

Replace the  CASE WHEN @OrderBy = 'SampleDT' THEN SampleDT with the following line 
CASE WHEN @OrderBy = 'SampleDT' THEN CAST(SampleDT as VARCHAR(12))

After replacing if you execute the script, you can see the results without any issues,





 







 
Complete Script

DECLARE @tmpTBL table
(
    ID INT IDENTITY(1,1),
    SampleDT datetime
)
INSERT INTO @tmpTBL VALUES('2015-08-01');
INSERT INTO @tmpTBL VALUES('2015-08-02');
INSERT INTO @tmpTBL VALUES('2015-08-03');
INSERT INTO @tmpTBL VALUES('2015-08-04');

Declare @SordDirection CHAR(1); SET @SordDirection = 'D';
Declare @OrderBy VARCHAR(10);SET @OrderBy = 'SampleDT';

        SELECT * FROM @tmpTBL
ORDER BY
        CASE WHEN @SordDirection ='D' THEN 'A'
        ELSE
        CASE WHEN @OrderBy = 'SampleDT' THEN CAST(SampleDT as VARCHAR(12))
        END
        END ASC,
        CASE WHEN @SordDirection='A' THEN 'D'
        ELSE
        CASE WHEN @OrderBy = 'SampleDT' THEN CAST(SampleDT as VARCHAR(12))
        END
        END DESC


Saturday, May 16, 2015

Sql Server order by month name


Following Script will order the result in actual calendar month order.

SELECT *  FROM [dbo].[tablename]
ORDER BY DATEPART(mm,CAST([DateFieldColumnName] + ' 1900' AS DATETIME)) ASC


Thursday, October 23, 2014

How To retain aspajax:CalendarExtender selected value after postback




Try This below snippet to retain the CalendarExtender selected value after postback


if(isPostback)
{
           YourCalendarExtender.SelectedDate =
                    DateTime.ParseExact(YourTextBox.Text, YourCalendarExtender.Format, null);
}


Thursday, September 25, 2014

Command Prompt How to list all files in a folder as well as sub-folders in windows


Step 1 : Goto required folder in Command prompt.

Step 2: Type the following command    dir /b /s 

              /b - User bare format
              /s - Lists the files in the directory that you are in and all sub directories after that directory

              Step 2 will list all the files reside in the parent folder and it's sub folder.

Step 3: To get the specific file give the appropriate file extension. for example if we need sql file to be listed, give the following command dir /b /s *.sql | sort


Creative Commons License
Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.