SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Working with Date and Time : Sql Server

Get Time in Hour:Minute or Hour:Minute:Second format (24 hours)

SELECT
  CONVERT(VARCHAR(8) , GETDATE() , 108) AS  HourMinuteSecond,
  CONVERT(VARCHAR(5) , GETDATE() , 108) AS HourMinute ,
  CONVERT(TIME,GETDATE()) AS HourMinuteSecondNanoSec --for Sql Server 2008

Get Time in Hour:Minute – 12 Hours Format

 SELECT SUBSTRING(CONVERT(varchar, getdate(), 100), 13, 2) + ':'
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 16, 2) + ' '
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 18, 2) AS MyTime

Reference:http://pcunleashed.com/sql-server/get-12-hour-time-from-datetime-datatype-in-sql-server-2005/

Get Date

  SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY], --US Format
  CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] --British / French Format

Get Date Time

SELECT CONVERT(VARCHAR(20), GETDATE(), 100) [Mon DD YYYY HH:MIAM (or PM)]
, CONVERT(VARCHAR(26), GETDATE(), 109) [Mon DD YYYY HH:MI:SS:sssAM (or PM)]
, CONVERT(VARCHAR(24), GETDATE(), 113) [DD Mon YYYY HH:MM:SS:sss(24h)]
, convert(varchar, getdate(), 120) [yyyy-mm-dd hh:mm:ss(24h)]
, convert(varchar, getdate(), 121) [yyyy-mm-dd hh:mm:ss.mmm]

Happy Coding 🙂

Leave a comment »

SQL Server Schema Binding Views

In SQL Server, views are not bound to the schema of the base tables by default. In such case we may change the schema of the base table at any time, regardless of the fact that the associated view may or may not work with the new schema. We can even drop the underlying table while keeping the associated view without any warning. In this case when the view is used, we will get an invalid object name error for the base table.

So if you want to create an index on a view or you want to preserve the base table schema once a view has been defined, in both these cases you have to use the “WITH SCHEMABINDING” clause to bind the view to the schema of the base tables.

Read further:

http://www.mssqltips.com/sqlservertip/1610/sql-server-schema-binding-and-indexed-views/

Leave a comment »

Get All Stored Procedures, Tables : Database Comparision

Select name,type_desc from database1.sys.objects where name  not in
(Select name From database1.sys.objects)
and type in( 'U', 'V', 'FN', 'IF', 'P')
order by type

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table–
UQ = UNIQUE constraint (type is K)
V = View–
X = Extended stored procedure

[/code]

Leave a comment »