how to prevent IE back button click using java script in an asp.net page

write the following code to achieve IE back button disabling

<script type="text/javascript">
        function noBack() {
            window.history.forward();
        }
        noBack();
        window.onload = noBack;
        window.onpageshow = function (evt) {
            if (evt.persisted) noBack();
        }
        window.onunload = function () {
            void (0);
        }
    </script>

how to select one check box at a time using java script in asp.net page when using a check box list

following javascript can control the selection of one check box at a time

function SelectOne(event, cname) {
            if (!event)
                event = window.event;
            var listItem = event.target || event.srcElement;
            var cbl = document.getElementById(cname);
            var listItems = cbl.getElementsByTagName('input');
            for (var i = 0; i < listItems.length; i++) {
                if (listItems[i] != listItem) {
                    listItems[i].checked = false;
                }
            }
        }

Usage:

<asp:CheckBoxList ID="chkRegt" runat="server" RepeatDirection="Horizontal" RepeatLayout="Flow"
    Onclick="SelectOne(event,'ctl00_MainContent_chkRegt');readCBLReg('ctl00_MainContent_chkRegt')">
    <asp:ListItem Text="Yes">Yes</asp:ListItem>
    <asp:ListItem Text="No">No</asp:ListItem>
</asp:CheckBoxList>

how to get a control name using javascript in asp.net page which uses a master page

To get a control name placed inside an asp.net page which uses a master page.

if (document.getElementById("<%=txtDtRecvd.ClientID %>").value == "") {

                alert("Please provide Received Date.");

                document.getElementById("<%=txtDtRecvd.ClientID %>").focus();

                return false;

            }

else

{
//
}

to hide or un hide a panel at server side in asp.net

To hide and un hide a panel in asp.net,
use the following code block

pnl.Attributes.Add("style", "display:none")
pnl.Attributes.Add("style", "display:block")

how to make a text box read only at server side in asp.net

To make a text box readonly at runtime or server side

txtName.Attributes.Add("readonly", "readonly")

how to create a Cursor in sql server

To create a cursor in sql server

DECLARE @uid VARCHAR(500)
DECLARE @AgtName VARCHAR(500)

DECLARE curSample SCROLL CURSOR
FOR
SELECT UserID,AgentName FROM TestTable
OPEN curSample
FETCH NEXT FROM curSample INTO @uid,@AgtName

WHILE (@@FETCH_STATUS <> -1)
BEGIN

Update TestTable SET UserID='Test' WHERE UserID = @uid

FETCH NEXT FROM curSample INTO @uid,@AgtName
END

CLOSE curSample
DEALLOCATE curSample

how to get current date in dd/mm/yyyy format

To get current date in dd/mm/yyyy format

DateTime.Now.ToString("dd/MM/yyyy")

how to create an empty data table in asp.net

to create an empty data table

Private Function CreateEmptyDT() As DataTable
        Dim DT As New DataTable()
        DT.Columns.Add("RowId")
        DT.Columns.Add("CourseDt")
        DT.Columns.Add("CourseTime")
        DT.Columns.Add("CreditHours")
        Return DT
    End Function

how to delete a row from dataset / datatable based on a condition

some times we need to manipulate the dataset or datatable returned from database

Dim SqlDS As New DataSet
For Each row As DataRow In ds.Tables(0).Rows
    If row.Item("Id") <> "blabla" Then
        row.Delete()
    End If
Next

ds.Tables(0).AcceptChanges()

how to check for IsDBNull when assiging a value from dataset or datatable to a control

To check for IsDBNull value before assigning a value to the control

lblCagt.Text = IIf(IsDBNull(dsAgent.Tables(0).Rows(0)("CompanyName")), "", dsAgent.Tables(0).Rows(0)("CompanyName"))

how to find a control name in gridview inside template field

To find a control name in gridview inside template field

<asp:TemplateField HeaderText="Training Date">
<ItemTemplate>
<asp:Label ID="lblCourseDate" runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>

write the following code in row databound of gridview

If e.Row.RowType = DataControlRowType.DataRow Or e.Row.RowType = ListItemType.AlternatingItem Or e.Row.RowType = ListItemType.Item  Then
Dim lblCourseDate As Label = e.Row.FindControl("lblCourseDate")
End If

how to enable Intellisense in skin files in asp.net themes

To enable Intellisense in skin files in asp.net themes

In Visual Studio go to Tools | Options | Text Editor | File Extensions and type skin in text box shown under Extension and select User Control Editor from the dropdown next to it. and click Apply.

Close and Open your skin file, now you can see the intellisense enabled in your skin file

how to find a control inside gridview

To find a lable placed inside an item template, use the following line of code

Dim lbl As Label = e.Row.FindControl("lblName")

how to Check IsDBNUll when assigning a value from dataset or data table

To check for IsDBNUll when assigning a value from dataset or data table

lbl.Text = IIf(IsDBNull(dsAgent.Tables(0).Rows(0)("Name")), "", dsAgent.Tables(0).Rows(0)("Name"))

how to change / alter the data type of a particular column in sql table

To alter the data type of a particular column in sql table

ALTER TABLE TableName ALTER COLUMN COLUMNNAME DATATYPE

how to convert Lower Case Characters to Upper Case Characters in sql

To convert Lower Case Characters to Upper Case Characters

SELECT UPPER(ColumnName) AS COL1 FROM TableName

how to convert Upper Case Characters to Lower Case Characters in sql

To convert Upper Case Characters to Lower Case Characters

SELECT LOWER(ColumnName) AS COL1 FROM TableName

how to get the last day of current year in sql server

To get the last day of current year using sql

SELECT DATEADD(MS,-3,DATEADD(YY,0,DATEADD(YY,DATEDIFF(YY,0,GETDATE())+1,0))) 'Year End'

how to get the first day of current year in sql server

to get the first day of current year using sql

SELECT DATEADD(YY,DATEDIFF(YY,0,GETDATE()),0)

how to get the last day of a month in sql

To get the last day of a month in sql

SELECT DATEADD(MS,- 3,DATEADD(M,0,DATEADD(M,DATEDIFF(M,0,GETDATE())+1,0))) 'Last Day'

how to get the first day in a month using sql

To get the first day in a month using sql

SELECT DATEADD(M,DATEDIFF(M,0,GETDATE()),0) 'First Day in a Month'

how to get the Last Day of Current Week in sql

To get the Last Day of Current Week in sql server

SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),6)

how to get first day of current week in sql

To get first day of current week

SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0)

how to get yesterday's date in sql server

To get yesterday's date in sql server

SELECT DATEADD(DAY,-1,GETDATE()) 'Yesterday'

how to get today's date in sql server

To get today's date in sql server

SELECT GETDATE() AS 'Today'

substring in sql server

substring in sql server

SELECT out = SUBSTRING('aBCDefgh',2,3)

output: BCD

how to get the last day of a month in sql server

To get last day of a month in sql server

SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),103)

how to get the first day of a month using sql server

we can get the first day in a month using the query

SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),103)

how to get the list of stored procedures in a database

To get the complete list of stored procedures in a database

SELECT * FROM SYS.PROCEDURES

how to get the list of all table names in a database

To get the complete list of tables in a particular database in sql server

SELECT * FROM SYS.TABLES

Keyboard Shortcut Keys for Visual Studio

Ctrl-Space bar - To see intelligence dialog

Ctrl-Shift-S - Saves all files and projects

Ctrl-P - Displays the Print dialog

F7 - Switches from the design view to the code view in the editor

Shift-F7 - Switches from the code view to the design view in the editor

Alt-Shift-A - Add Existing Item(file) to selected project

Ctrl-Shift-A - Add New Item(file) to selected project

Shift-F9 - Display the selected item quick output means contains value while debugging

F12 - Moves the cursor to the selected method, variable, class definition.

Shift-F12 - Finds the reference to the selected method, variable, class or the item under the cursor

Ctrl-End --> Moves the cursor to the end of the document

Ctrl-Home --> Moves the cursor to the start of the document

Ctrl-G --> Displays the Go to Line dialog. If the debugger is running, the dialog also lets you specify addresses or function names to go to

Ctrl-Down Arrow  --> Scrolls text down one line but does not move the cursor. This is useful for scrolling more text into view without losing your place

Ctrl-Up Arrow --> Scrolls text up one line but does not move the cursor

Ctrl-Right Arrow --> Moves the cursor one word to the right

Ctrl-Left Arrow --> Moves the cursor one word to the left

Shift-Tab --> Moves current line or selected lines one tab stop to the left

Ctrl-G --> Go to Particular line

Ctrl-K, Ctrl-C --> To comment the selected lines/current line

Ctrl-K, Ctrl-U --> To Un comment the selected lines/current line

Ctrl-T --> Swaps the characters on either side of the cursor. (For example, AC|BD becomes AB|CD.)

Ctrl-M, Ctrl-O --> Automatically determines logical boundaries for creating regions in code, such as procedures, and then hides them.

Ctrl-M, Ctrl-L --> Collapses all regions in the current document

Ctrl-K, Ctrl-\ --> Removes horizontal whitespace in the selection or deletes whitespace adjacent to the cursor if there is no selection

Ctrl-L --> Cuts all selected lines or the current line if nothing has been selected to the clipboard

Ctrl-Enter --> Inserts a blank line above the cursor

Ctrl-Shift-Enter --> Inserts a blank line below the cursor

Shift-Alt-T --> Moves the line containing the cursor below the next line

Ctrl-J --> Lists members for statement completion when editing code

Ctrl-U --> Changes the selected text to lowercase characters

Ctrl-Shift-U --> Changes the selected text to uppercase characters

Ctrl-Shift-Spacebar --> Displays a tooltip that contains information for the current parameter

Ctrl-R, Ctrl-W --> Shows or hides spaces and tab marks

Ctrl-Delete --> Deletes the word to the right of the cursor

Ctrl-Backspace --> Deletes the word to the left of the cursor

Ctrl-Shift-B --> Builds the solution

Ctrl-N --> Displays the New File dialog

Ctrl-Shift-N --> Displays the New Project dialog

Ctrl-O --> Displays the Open File dialog

Ctrl-Shift-O --> Displays the Open Project dialog

Shift-Alt-A --> Displays the Add Existing Item dialog

Ctrl-Shift-A --> Displays the Add New Item dialog

Ctrl-M-O --> Collapse all the methods, classes, regions in the current code behind or class file

Ctrl-M-P or Ctrl-M-L --> Expands all the methods, classes, regions in the current code behind or class file

Ctrl-F --> Displays the Find dialog

Ctrl-H --> Displays the Replace dialog

Ctrl-Shift-F --> Find the reference of selected item into entire solution.

Ctrl-Tab --> Move from one opened file to another opened file in visual studio.

F9 --> Sets or removes a breakpoint at the current line

F5 --> Runs the code with invoking the debugger.

Ctrl-F5 --> Runs the code without invoking the debugger.

F4 or Alt-Enter --> Displays the Properties window, which lists the design-time properties and events for the currently selected item

Ctrl-Alt-L --> Displays the Solution Explorer

Ctrl-Alt-X --> Displays the Toolbox

Ctrl-Alt-I --> Displays the Immediate window, where you can find the controls or variables values or can do data manipulation during debugging

to split a string into sub strings of an array or to find a character in a string

Dim str() As String = TextBox1.Text.Split(".")
Dim val As String = str(0)

bulk copy from excel to sql server in asp.net

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

Dim strConString As String = String.Empty
Dim Excelds As DataSet
Excelds = New DataSet()
            strConString = System.Configuration.ConfigurationManager.ConnectionStrings("Con").ConnectionString
            Dim bcp As SqlBulkCopy = New SqlBulkCopy(strConString)
            bcp.DestinationTableName = "dbo.TblBulk"
            'mapping required if source and destination column names are different
            bcp.ColumnMappings.Add("NO", "SeqNo")
            bcp.ColumnMappings.Add("UserId", "UserId")
            bcp.ColumnMappings.Add("Name", "Name")
            bcp.ColumnMappings.Add("DateCreated", "DateCreated")
            bcp.WriteToServer(Excelds.Tables(0))

to generate an excel file in asp.net

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

Private Sub GenerateExcel()
        Try
                    Dim excelApp As Microsoft.Office.Interop.Excel.Application
            Dim workBook As Microsoft.Office.Interop.Excel.Workbook
            Dim workSheet As Microsoft.Office.Interop.Excel.Worksheet
            Dim sqlstring As String = String.Empty
            Dim sqlcon As SqlConnection
            Dim strConString As String
            Dim sqlcmd As SqlCommand
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            Dim ExcelDT As DataTable
            Dim RowNum As Integer = 0
            Dim ColumnNum As Integer = 0
                       Dim MyExcel As Object = CreateObject("Excel.Application")
            If MyExcel Is Nothing Then
                Alert("Unable to generate excel file")
                Return
            End If
            System.Runtime.InteropServices.Marshal.ReleaseComObject(MyExcel)
            MyExcel = Nothing
            strConString = System.Configuration.ConfigurationManager.ConnectionStrings("CONSTRING").ConnectionString
            sqlstring = "SELECT * FROM TblTest"
            sqlcon = New SqlConnection(strConString)
            sqlcmd = New SqlCommand(sqlstring, sqlcon)
            da = New SqlDataAdapter(sqlcmd)
            ds = New DataSet()
            da.Fill(ds, "details")
            ExcelDT = ds.Tables("details")
            sqlcon.Close()
            sqlcmd.Dispose()
            sqlcon.Dispose()
            If Not Directory.Exists(Server.MapPath("~/Data")) Then
                Directory.CreateDirectory(Server.MapPath("~/Data"))
            End If
            Dim TargetFile As String = Server.MapPath("~/Data/") & DateTime.Now.ToString("ddMMyyyy-HHmmss.fff") & ".xlsx"
            excelApp = New Microsoft.Office.Interop.Excel.Application
            workBook = excelApp.Workbooks.Add()
            workSheet = workBook.Worksheets("Sheet1")
            workSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape
            With workSheet
                RowNum = 1
                .Range("A" & RowNum, "Q" & RowNum).Font.Bold = True
                .Range("A" & RowNum, "Q" & RowNum).Font.ColorIndex = 5
                .Range("A" & RowNum, "Q" & RowNum).Font.Size = 9
                .Range("A" & RowNum, "Q" & RowNum).Font.Name = "Tahoma"
                                .Cells(RowNum, 1).value = "No."
                For ColumnNum = 1 To ds.Tables("details").Columns.Count
                    If (ColumnNum = 1) Then
                        .Cells(RowNum, ColumnNum + 1).value = "Col1"
                        workSheet.Columns._Default(ColumnNum + 1).ColumnWidth = 25
                    ElseIf (ColumnNum = 2) Then
                        .Cells(RowNum, ColumnNum + 1).value = "Col2"
                    ElseIf (ColumnNum = 3) Then
                        .Cells(RowNum, ColumnNum + 1).value = "Col3"
                    ElseIf (ColumnNum = 4) Then
                        .Cells(RowNum, ColumnNum + 1).value = "Col4"
                    ElseIf (ColumnNum = 5) Then
                        .Cells(RowNum, ColumnNum + 1).value = "Col5"
                    ElseIf (ColumnNum = 6) Then
                        .Cells(RowNum, ColumnNum + 1).value = "Col6"
                    ElseIf (ColumnNum = 7) Then
                        .Cells(RowNum, ColumnNum + 1).value = "Col7"
                    Else
                        .Cells(RowNum, ColumnNum + 1).value = ds.Tables("details").Columns(ColumnNum - 1).ColumnName
                    End If
                    workSheet.Columns._Default(ColumnNum + 1).ColumnWidth = 25
                Next
                .Range("A" & RowNum, "Q" & RowNum).Font.Bold = True
                .Range("A" & RowNum, "Q" & RowNum).Font.ColorIndex = 5
                .Range("A" & RowNum, "Q" & RowNum).Font.Size = 8
                RowNum = RowNum + 1

                For Each DR As DataRow In ds.Tables("details").Rows
                    .Cells(RowNum, 1).value = ds.Tables("details").Rows.IndexOf(DR) + 1
                    For ColumnNum = 1 To ds.Tables("details").Columns.Count
                        .Cells(RowNum, ColumnNum + 1).value = DR(ColumnNum - 1).ToString().Trim()
                    Next
                    RowNum = RowNum + 1
                Next
            End With
            workSheet.SaveAs(TargetFile)
            workBook.Close()
            workBook = Nothing
            excelApp.Quit()
            excelApp = Nothing
            Alert("Excel File Generated Succesfully!")
        Catch ex As Exception
            Alert(ex.ToString)
        End Try
    End Sub

for alert
Public Sub Alert(ByVal msg As String)
        Page.ClientScript.RegisterStartupScript([GetType], "Alert", "<script>alert('" & msg & "')</script>")
    End Sub

to add / append / ammend a new column to existing excel file in asp.net

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

Private Sub ExcelColumnAppend(ByVal ExcelData As DataSet)
        Dim excelApp As Microsoft.Office.Interop.Excel.Application
        Dim workBook As Microsoft.Office.Interop.Excel.Workbook
        Dim workSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim ds As DataSet
        Dim RowNum As Integer = 0
        Dim ColumnNum As Integer = 0
        Try
            ds = New DataSet
            ds = ExcelData
            Dim MyExcel As Object = CreateObject("Excel.Application")
            If MyExcel Is Nothing Then
                WebMessage("Unable to generate excel file")
                Return
            End If
            System.Runtime.InteropServices.Marshal.ReleaseComObject(MyExcel)
            MyExcel = Nothing
            Dim SourceFilePath As String = Server.MapPath("~/Data/Test.xlsx")
            Dim DestFilePath As String = Server.MapPath("~/Data/Test_Modified.xlsx")
            excelApp = New Microsoft.Office.Interop.Excel.Application
            workBook = excelApp.Workbooks.Open(SourceFilePath)
            workSheet = workBook.Worksheets("Sheet1")
            workSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape
            excelApp.DisplayAlerts = False
            With workSheet
                RowNum = 2
                .Cells(1, 21).value = "AppendedColumn"
                For Each DR As DataRow In ds.Tables(0).Rows
                        For ColumnNum = 21 To ds.Tables(0).Columns.Count
                            .Cells(RowNum, 21).value = DR(21 - 1).ToString().Trim()
                        Next
                    RowNum = RowNum + 1
                Next
            End With
            workSheet.SaveAs(DestFilePath)
            workBook.Close()
            workBook = Nothing
            excelApp.Quit()
            excelApp = Nothing
           'To Open file
            Response.AddHeader("Content-Disposition", "attachment; filename=" & Path.GetFileName(DestFilePath))
            Response.WriteFile(DestFilePath)
            Response.Flush()
            Response.Close()
         Catch ex As Exception
            WebMessage(ex.ToString())
        End Try
    End Sub

how to Change the value(s) of a particular column in dataset or datatble returned

To Change the value(s) of a particular column in dataset or datatble in asp.net

For Each row As DataRow In Excelds.Tables(0).Rows
   Dim dtvView As DataView = ds.Tables(0).DefaultView
   dtvView.Item(RowNo).Row.Item("Desc") = "BlaBla.."
   ds.Tables(0).AcceptChanges()
   RowNo += 1
Next

how to add a new column to dataset or datatble returned

To add a new column to dataset or datatble

Dim ds As DataSet
ds = New DataSet()
ds.Tables(0).Columns.Add("ProductDesc")
ds.Tables(0).AcceptChanges()

to get excel sheet names from an excel file

If you want to list out all the sheet names in an excel file, use the following code

Dim ExcelPath As String = Server.MapPath("~/Data/Test.xlsx")
            ExcelconnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelPath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
            ExcelConn = New OleDbConnection(ExcelconnString)
            If ExcelConn.State = ConnectionState.Closed Then ExcelConn.Open()
            dt = ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            For Each row As DataRow In dt.Rows
                SheetName = row("TABLE_NAME").ToString
                Exit For
            Next

export gridview data directly to excel file

To export gridview to excel in asp.net

Protected Sub ExportToExcel()
        Try
            Dim stringWrite As StringWriter
            Dim htmlWrite As HtmlTextWriter
            Dim frm As HtmlForm = New HtmlForm()
            Dim style As String = "<style>td{ mso-number-format: \@; }</style>"

            Response.ClearHeaders()
            Response.AppendHeader("content-disposition", "attachment;filename=TEST.xls")
            Response.Charset = String.Empty '""
            Response.ContentType = "application/vnd.ms-xlsx"
            stringWrite = New StringWriter
            htmlWrite = New HtmlTextWriter(stringWrite)
            Controls.Add(frm)
            frm.Controls.Add(gvReport)
            frm.RenderControl(htmlWrite)
            Response.Write(style)
            Response.Write(stringWrite.ToString())
            Response.End()
        Catch ex As Exception
            WebMessage(ex.ToString)
        End Try
    End Sub

to get the list of files from a directory in asp.net

To get the complete list of pdf files under specified directory

Private Sub GetFileList()
        Dim dir As New DirectoryInfo("D:\Sai")
        Dim dirinfo As FileInfo() = dir.GetFiles("*.pdf")
        Dim FileName As FileInfo
        Dim path As String
        If dir.Exists = True Then
            For Each FileName In dirinfo
                path = dir.FullName & "\" & FileName.ToString
               'Do Something
            Next
        End If
    End Sub

To retrieve the pdf stored in sql server as varbinary

Private Sub ReadPdfFromSQL()
        Try
            strConString = System.Configuration.ConfigurationManager.ConnectionStrings("CONSTRING").ConnectionString
            SqlCon = New SqlConnection(strConString)
            SqlCmd = New SqlCommand()
            SqlCmd.CommandType = CommandType.StoredProcedure
            'SqlCmd.Parameters.AddWithValue("@Type", "S")
            SqlCmd.Connection = SqlCon
            SqlCmd.CommandText = "SP_ReadPdfData"
            SqlCon.Open()
            sqldr = SqlCmd.ExecuteReader()
            Dim Buffer() As Byte = Nothing
            If sqldr.HasRows Then
                While sqldr.Read
                    Buffer = (sqldr.Item("PdfData"))
                End While
            End If
            SqlCon.Close()
            SqlCmd.Dispose()
            SqlCon.Dispose()
            'to open as an attachment
            Response.AddHeader("Content-Disposition", "attachment; filename=abc.pdf")
            Response.ContentType = "application/pdf"
            Response.BinaryWrite(Buffer)
            Response.Flush()
            Response.Close()
            Response.End()
            'to open in browser itself
            Response.ClearHeaders()
            Response.ClearContent()
            Response.ContentType = "application/pdf"
            Response.BinaryWrite(Buffer)
            Response.Flush()
            Response.Close()
            Response.End()
        Catch ex As Exception
            lblError.Text = ex.ToString()
        Finally
            SqlCon.Close()
            SqlCmd.Dispose()
            SqlCon.Dispose()
        End Try
    End Sub

how to auto refresh the page using javascript

write the following javascript in your .aspx page to auto refresh the page in particular interval of time

<head runat="server">
    <title></title>
    <script type="text/javascript">
        var timeout = setTimeout("location.reload(true);", 3000); //3000 - 3 seconds
        function resetTimeout() {
            clearTimeout(timeout);
            timeout = setTimeout("location.reload(true);", 3000);
        }
    </script>
</head>

to get the auto identity number generated in a current code execution

DECLARE @ID AS BigInt

Set @ID=Scope_Identity()

how o create a temp table in sql

CREATE TABLE #tmpTbl(A VARCHAR(200))

how to get complete list of user defined stored procedures on a particular DB

SELECT * FROM SYS.procedures WHERE NAME NOT LIKE 'SP_%DIAGRAM%' ORDER BY create_date desc

how to get the positive value of a number in sql

to get the positive value out of a number in sql server
use the following query

SELECT ABS(-78)
returns 78

to search a pattern with in an expression and to get the position use the following sql query

to search a pattern with in an expression and to get the position
use the following sql query

SELECT PATINDEX('%BC%','ABCD')
SELECT PATINDEX('%[s,S]erver%', 'Microsoft SQL Server SQL')
SELECT PATINDEX('%[1-9]163%', '12345A916303SQL')
SELECT PATINDEX('%[.]%', 'sample.xls')

if returns 0, no pattern found
if returns >0, pattern found

how to replace the selected characters with custom text

to replace the selected characters with custom text using sql server,
use the following sql query

SELECT REPLACE ('BLABLA IS A TEST!','IS','was')

return value : BLABLA was A TEST!

how to ammend custom text in a selected position in sql server

to ammend custom text in a selected position in sql server,
use the following sql query

SELECT STUFF ('BLABLA IS A TEST', 8, 2, 'replaced')

return value : BLABLA replaced A TEST

how to get right 3 characters from a string in sql server

to get right 3 characters from a string,
the following code will help out

SELECT RIGHT('ASPDOTNET',3)

return value : NET

to get left 4 characters from a string in sql

To get the left characters from a string using sql server

QUERY
SELECT LEFT('ASPDOTNET',4)

RESULT
ASPD

To reverse a string

to reverse a given string in sql server, use the following query

Query

SELECT REVERSE('ASPDOTNET')

Result

TENTODPSA

how to get the no of days between two dates

to get the no of days between two dates,
use the following sql query

SELECT DATEDIFF(DAY,'2013-04-12',GETDATE())

return value : 265

add months to date

To add no.of months to a given date, use the following query

QUERY
SELECT DATEADD(M,2,GETDATE())

RESULT
if current month is august, the result is as follows
2013-10-01 14:57:41.143

get month name from date part

To get the month name from a given date, u should use the following query block

Query
SELECT DATENAME(MONTH, GETDATE())

Result
August

how to get the year part from a date

Query
SELECT YEAR(GETDATE())

Return value
2014

how to get weekend days from current month in sql server

WITH CTE(DATE, Cnt)AS(SELECT DATEADD(DAY, -DAY(GETDATE()-1), GETDATE()), 1
UNION ALL
SELECT DATE+1, Cnt+1 FROM CTE WHERE Cnt < (SELECT DAY(DATEADD(DAY, -DAY(DATEADD(MM, 1, GETDATE())), DATEADD(MM, 1, GETDATE())))))
SELECT DATENAME(WEEKDAY, DATE) AS WEEKDAYS, cnt INTO #MaskTable FROM CTE OPTION(MAXRECURSION 30)
SELECT * FROM #MaskTable WHERE WEEKDAYS IN('Saturday', 'Sunday')
-----------------------------------------
Saturday 6
Sunday 7
Saturday 13
Sunday 14
Saturday 20
Sunday 21
Saturday 27
Sunday 28

date convertion to yyyymmdd

To convert the date to yyyymmdd format in sql server

Query
SELECT CONVERT(VARCHAR(20), GETDATE(),112)

Return Value
20130801

convert date to yyyy/mm/dd in sql server

To convert the date to yyyy/mm/dd in sql server

Query
SELECT CONVERT(VARCHAR(20), GETDATE(),111)

Result
2013/08/01

how to convert date to dd-mm-yyyy format

To convert a given date to dd-mm-yyyy format in sql server,
use the following query

Query
SELECT CONVERT(VARCHAR(30), GETDATE(),110)

Return value
08-01-2013

To get current time in 24 hour format

Query
SELECT CONVERT(VARCHAR(20), GETDATE(),108)
Result
14:47:34

how to convert a date to dd monthname yyyy format

To convert a date to dd monthname yyyy format in sql

Query
SELECT CONVERT(VARCHAR(20), GETDATE(),106)

Result
01 Aug 2013

Sql Server - Date convertion to dd-mm-yyyy

To Convert the date to dd-mm-yyyy format, use the following query

Query
SELECT CONVERT(VARCHAR(20), GETDATE(),105)

Return Value
01-08-2013

how to convert date to dd.mm.yyyy format

To Convert the date to dd/mm/yyyy format, use the following query

Query
SELECT CONVERT(VARCHAR(20), GETDATE(),104)

Return Value
01.08.2013

Date convertion to dd/mm/yyyy in sql

To Convert the date to dd/mm/yyyy format, use the following query

Query
SELECT CONVERT(VARCHAR(10), GETDATE(),103)

Return Value
01/08/2013

convert date to yyyy.dd.mm format in sql

To convert the given date to a format like yyyy.dd.mm

Query
SELECT CONVERT(VARCHAR(11), GETDATE(),102)

Return Value
2013.08.01

how to convert date to mm/dd/yyyy format

to convert a given date to mm/dd/yyyy format in sql server,
use the following query

Query
SELECT CONVERT(VARCHAR(11), GETDATE(),101)

Return value
08/01/2013

how to get the name of the day in sql server

To get the name of a day in sql server

Query
SELECT DATENAME(weekday,getdate())