Date Format using CONVERT function in SQL Server

Today, i would like to list out various styles of date display formats. These are very helpful for the Front-end application programmers where they want to display different date formats.

Various date style formats can be produced with CONVERT function.
CONVERT function need 3 parameters.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
The first parameter is return data type, second parameter is expression, third parameter which is optional parameter defines the style.

NOTE: Do not apply CONVERT function on date column when it is Indexed.
with out waiting lets look at the various styles of date values.
The below query produces the output in
MON DD YYYY HH:MIAMPM format.
SELECT CONVERT(VARCHAR(30),GETDATE(),100)
--Output (MON DD YYYY HH:MIAMPM)
--Jul 7 2009 2:19PM
The below query produces the output in MM/DD/YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),101)
--Output (MM/DD/YYYY)
--07/07/2009
The below query produces the output in YYYY.MM.DD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),102)
--Output (YYYY.MM.DD)
--2009.07.07
The below query produces the output in DD/MM/YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),103)
--Output (DD/MM/YYYY)
--06/07/2009
The below query produces the output in DD.MM.YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),104)
--Output (DD.MM.YYYY)
--06.07.2009
The below query produces the output in DD-MM-YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),105)
--Output (DD-MM-YYYY)
--06-07-2009
The below query produces the output in DD MON YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),106)
--Output (DD MON YYYY)
--06 Jul 2009
The below query produces the output in MON DD,YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),107)
--Output (MON DD,YYYY)
--Jul 06, 2009
The below query produces the output in HH24:MI:SS format.
SELECT CONVERT(VARCHAR(30),GETDATE(),108)
--Output (HH24:MI:SS)
--14:24:20
The below query produces the output in MON DD YYYY HH:MI:SS:NNN AMPM format.
Use 113 style to get date and time with nano seconds in AM/PM format.
SELECT CONVERT(VARCHAR(30),GETDATE(),109)
--Output (MON DD YYYY HH:MI:SS:NNN AMPM)
--Jul 7 2009 2:24:35:490PM
The below query produces the output in MM-DD-YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),110)
--Output (MM-DD-YYYY)
-- 07-07-2009
The below query produces the output in YYYY/MM/DD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),111)
--Output (YYYY/MM/DD)
--2009/07/07
The below query produces the output in YYYYMMDD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),112)
--Output (YYYYMMDD)
--20090707
The below query produces the output in MON DD YYYY HH24:MI:SS:NNN format.
Use 113 style to get date and time with nano seconds in 24 Hour Format.
SELECT CONVERT(VARCHAR(30),GETDATE(),113)
--Output (MON DD YYYY HH24:MI:SS:NNN)
--07 Jul 2009 14:26:24:617
The below query produces the output in HH24:MI:SS:NNN format.
Use 114 Style to extract Time part with nano seconds in 24 Hour Format.
SELECT CONVERT(VARCHAR(30),GETDATE(),114)
--Output (HH24:MI:SS:NNN)
--14:26:48:953

Information Schema Views in SQL Server

In this article, I would like to list out the SQL Server metadata tables which we need in our day-to-day work.
SELECT * FROM Information_Schema.CHECK_CONSTRAINTS
-- Contains one row for each CHECK constraint in the current database.
SELECT * FROM Information_Schema.COLUMN_DOMAIN_USAGE
-- Contains one row for each column, in the current database, that has a user-defined data type.
SELECT * FROM Information_Schema.COLUMN_PRIVILEGES
--Contains one row for each column with a privilege either granted to or by the current user in the current database
SELECT * FROM Information_Schema.COLUMNS
--Contains one row for each column accessible to the current user in the current database.
SELECT * FROM Information_Schema.CONSTRAINT_COLUMN_USAGE
--Contains one row for each column, in the current database, that has a constraint defined on it.
SELECT * FROM Information_Schema.CONSTRAINT_TABLE_USAGE
--Contains one row for each table, in the current database, that has a constraint defined on it.
SELECT * FROM Information_Schema.DOMAIN_CONSTRAINTS
--Contains one row for each user-defined data type, accessible to the current user in the current database, with a rule bound to it.
SELECT * FROM Information_Schema.DOMAINS
--Contains one row for each user-defined data type accessible to the current user in the current database.
SELECT * FROM Information_Schema.KEY_COLUMN_USAGE
--Contains one row for each column, in the current database, that is constrained as a key.
SELECT * FROM Information_Schema.PARAMETERS
--Contains one row for each parameter of a user-defined function or stored procedure accessible to the current user in the current database.
SELECT * FROM Information_Schema.REFERENTIAL_CONSTRAINTS
--Contains one row for each foreign constraint in the current database.
SELECT * FROM Information_Schema.ROUTINE_COLUMNS
--Contains one row for each column returned by the table-valued functions accessible to the current user in the current database.
SELECT * FROM Information_Schema.ROUTINES
--Contains one row for each stored procedure and function accessible to the current user in the current database.
SELECT * FROM Information_Schema.SCHEMATA
--Contains one row for each database that has permissions for the current user.
SELECT * FROM Information_Schema.TABLE_CONSTRAINTS
--Contains one row for each table constraint in the current database.
SELECT * FROM Information_Schema.TABLE_PRIVILEGES
--Contains one row for each table privilege granted to or by the current user in the current database.
SELECT * FROM Information_Schema.TABLES
--Contains one row for each table in the current database for which the current user has permissions.
SELECT * FROM Information_Schema.VIEW_COLUMN_USAGE
--Contains one row for each column, in the current database, used in a view definition.
SELECT * FROM Information_Schema.VIEW_TABLE_USAGE
--Contains one row for each table, in the current database, used in a view.
SELECT * FROM Information_Schema.VIEWS
--Contains one row for views accessible to the current user in the current database.

Interview Questions

.Net Interview Questions
ASP.NETASP.Net Interview Questions - Part 1
ASP.Net Interview Questions - Part 2
ASP.Net Interview Questions - Part 3
ASP.Net Interview Questions - Part 4
ASP.Net Interview Questions - Part 5
ASP.Net Interview Questions - Part 6
ASP.Net Interview Questions - Part 7
ASP.Net Interview Questions - Part 8
ASP.Net Interview Questions - Part 9
ASP.Net Interview Questions - Part 10
ASP.Net Interview Questions - Part 11
ASP.Net Interview Questions - Part 12
C# C# Interview Questions

Delete Duplicate records in sql server

WITH CTE (SecondCol,ThirdCol, DuplicateCount)
AS
(
SELECT SecondCol,ThirdCol,
ROW_NUMBER()
OVER(PARTITION BY SecondCol,ThirdCol ORDER BY secondCol)
AS DuplicateCount
FROM testtable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

how to remove the LOGFILE and perform some operations from cmd prompt

SELECT name,recovery_model_desc FROM sys.databases
and check whether the database is 'FULL' mode or 'SIMPLE'..if it s FULL we have to remove the LOGFILE of database.
go to database ...> detach.. next goto logfile and rename it..of that database
again we have to attach the only .mdf file (instead of .mdf and .ldf files to database)
=====================
sqlcmd -?
we can change the sqlserver sa password from cmd prompt very easily
EXEC SP_PASSWORD NULL,'venki','sa'
GO

Bind the Help To Project and Binding datasource to report viewver

Dim hlppath As String = Application.StartupPath & "\Ayuda\Espiga.chm"
System.Windows.Forms.Help.ShowHelp(Me, hlppath)


Private Sub InitReports(ByVal ReportName As String, ByVal DS As DataSet, ByVal ParameterName() As String, ByVal ParameterValue() As String)
Try
Dim ParamList As New Generic.List(Of Microsoft.Reporting.WinForms.ReportParameter)
Dim reportDatasource As New Microsoft.Reporting.WinForms.ReportDataSource
ReportViewer1.Clear()
ReportViewer1.Reset()
'Add Tables to LocalReport's DataSource
For Each table As DataTable In DS.Tables
Me.ReportViewer1.LocalReport.DataSources.Clear()
Me.ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WinForms.ReportDataSource(DS.DataSetName & "_" & table.TableName, table))
Next
'Load parameters
For i As Integer = 0 To ParameterName.Length - 1
ParamList.Add(New Microsoft.Reporting.WinForms.ReportParameter(ParameterName(i).Trim, ParameterValue(i).Trim))
Next i
'Embed the LocalReport to the ReportViewer
Me.ReportViewer1.LocalReport.ReportEmbeddedResource = Me.GetType().Namespace & "." & ReportName
'Set the parameters to the LocalReport
Me.ReportViewer1.LocalReport.SetParameters(ParamList)
Me.ReportViewer1.RefreshReport()
Catch ex As Exception
End Try
End Sub

Export the Grid data to Excel

Public Sub Export2Excel(ByVal grdView As DataGridView, ByVal fileName As String)
Dim myFile As String = fileName
Dim subtractBy As Integer, cellText As String
Dim fs As IO.StreamWriter = Nothing
Try
' Open the file and write the headers
fs = New IO.StreamWriter(myFile, False)
fs.WriteLine("")
fs.WriteLine("")
fs.WriteLine("")
' Create the styles for the worksheet
fs.WriteLine(" ")
' Style for the column headers
fs.WriteLine(" ")
fs.WriteLine(" ")
fs.WriteLine(" "ss:WrapText=""1""/>")
fs.WriteLine(" ")
fs.WriteLine(" ")
' Style for the column information
fs.WriteLine(" ")
fs.WriteLine(" ")
fs.WriteLine(" ")
fs.WriteLine(" ")
' Write the worksheet contents
fs.WriteLine("")
fs.WriteLine(" ")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" ", _
grdView.Columns.Item(i).Width))
Next
fs.WriteLine(" ")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" " & _
"{0}", _
grdView.Columns.Item(i).HeaderText))
Next
fs.WriteLine(" ")
' Check for an empty row at the end due to Adding allowed on the DataGridView
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
' Write contents for each cell
For i As Integer = 0 To grdView.RowCount - subtractBy
fs.WriteLine(String.Format(" ", _
grdView.Rows(i).Height))
For intCol As Integer = 0 To grdView.Columns.Count - 1
cellText = ""
If grdView.Item(intCol, i).Value IsNot DBNull.Value Then cellText = grdView.Item(intCol, i).Value
' Check for null cell and change it to empty to avoid error
If cellText = vbNullString Then cellText = ""
fs.WriteLine(String.Format(" " & _
"{0}", _
cellText.ToString))
Next
fs.WriteLine(" ")
Next
' Close up the document
fs.WriteLine(" ")
fs.WriteLine("")
fs.WriteLine("")
Catch ex As Exception
Throw ex
Finally
If fs IsNot Nothing Then fs.Close()
End Try
End Sub