Avoiding Date Format errors while writing SQL Queries

Many times we face the task of displaying records falling between two dates. We ask the user to enter the date range. The date format for user is dd/MM/yyyy (in India).

First assign the dates from the Text Box to some variables of Date type

  Dim tmpDateFrom As Date
  Dim tmpDateTo As Date
  Try
     tmpDateFrom = Date.ParseExact(txtDateFrom.Text, "dd/MM/yyyy",                           Globalization.CultureInfo.InvariantCulture)
     tmpDateTo = Date.ParseExact(txtDateTo.Text, "dd/MM/yyyy",                               Globalization.CultureInfo.InvariantCulture)
  Catch ex As Exception
     lblErrorMessage.Text = "Enter Date in dd/MM/yyyy format only!"
     pnlError.Visible = True
     Exit Sub
  End Try

When we need to insert the above dates in SQL query use the following syntax:

"WHERE (CONVERT(date, DOJ)>='" & Format(tmpDateFrom, "yyyy-MM-dd") & "' AND CONVERT(date, DOJ) <='" & Format(tmpDateTo, "yyyy-MM-dd") & "')"


When using parameters, use the following syntax:
 cmdInsert.Parameters.Add("@DateFrom", SqlDbType.Date)
 cmdInsert.Parameters("@DateFrom").Value = tmpDateFrom
 cmdInsert.Parameters.Add("@DateTo", SqlDbType.Date)
 cmdInsert.Parameters("@DateTo").Value = tmpDateTo

Display all constraints in all Tables of a Database Using SQL Query

Sometimes you may want to list all the constraints like Primary Keys, Foreign Keys etc. of all the tables in a database. Use the following SQL Query to achieve that


SELECT OBJECT_NAME(object_id) AS ConstraintName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
ORDER BY OBJECT_NAME(object_id)

Changing the Schema of All Tables

Sometimes we require to change the schema of all the tables in a database.

For example, you have a database whose tables are in dpu schema and now you want to change the schema to dbo,  use the folowing code:


DECLARE @sql Varchar(8000), @table Varchar(1000), @SchemaToChange Varchar(1000), @NewSchema Varchar(1000)

SET @SchemaToChange = 'dpu'
SET @NewSchema = 'dbo'

WHILE exists(SELECT * FROM sys.tables WHERE schema_name(schema_id) = @SchemaToChange)
BEGIN
SELECT @table = name from sys.tables WHERE object_id in
       (SELECT min(object_id) FROM sys.tables WHERE schema_name(schema_id) = @SchemaToChange)

SET @sql = 'alter schema ' + @NewSchema + ' transfer ' + @SchemaToChange + '.' + @table

EXEC(@sql)

END

Caution when using UNION

The UNION Clause is used to combine the output of two SQL Queries. But beware, using UNION will remove duplicate rows from the output. If you need to have all the rows from both the queries, use UNION ALL clause.

Example:

(SELECT A.CardNo as EntryCode, A.CardNo as BillNo, A.CardDate As BillDate,C.CustomerName As Details, ' ' As Inward,  B.Quantity As Outward, 'OnCard' as TransType FROM OnCard as A, OnCardDetails As B, MasterCustomers as C WHERE  A.CardNo=B.CardNo AND A.CustomerCode=C.CustomerCode AND B.ProductCode=28935)
UNION ALL
(SELECT A.BillNo as EntryCode, A.BillNo, A.BillDate, A.CustomerName As Details, ' ' As Inward, B.Quantity As Outward, 'Sale' as TransType FROM BillSale As A, BillSaleDetails as B WHERE A.BillNo=B.BillNo  AND B.ProductCode=28935 )

Display Primary Key Using SQL Query

If you want to know the primary key of any table, use the following SQL Query

SELECT * FROM sysobjects WHERE xtype='pk' AND parent_obj IN (SELECT id FROM sysobjects WHERE name='tablename')