Update only Date Part : Sql Server

Select *,
dateadd(dd,datediff(dd,SS.StartDateTime,'2012-07-19 00:00:00.000'),SS.StartDateTime)
from t_sessions S
INNER JOIN tIdx_ConferenceSessions CS
ON S.PK_Session = CS.FK_Session
inner join tIdx_SessionSettings SS
on s.PK_Session = SS.FK_Session
Where FK_Conference = 893

Working with Date and Time : Sql Server

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

[code language="sql"]
SELECT
CONVERT(VARCHAR(8) , GETDATE() , 108) AS HourMinuteSecond,
CONVERT(VARCHAR(5) , GETDATE() , 108) AS HourMinute ,
CONVERT(TIME,GETDATE()) AS HourMinuteSecondNanoSec --for Sql Server 2008
[/code]

Get Time in Hour:Minute - 12 Hours Format

[code language="sql"]
SELECT SUBSTRING(CONVERT(varchar, getdate(), 100), 13, 2) + ':'
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 16, 2) + ' '
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 18, 2) AS MyTime
[/code]

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


Get Date

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

Get Date Time

[code language="sql"]
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]
[/code]

Happy Coding :)

Working with Date and Time : Sql Server

Get Time in Hour:Minute or Hour:Minute:Second format (24 hours)
[code]
SELECT
CONVERT(VARCHAR(8) , GETDATE() , 108) AS HourMinuteSecond,
CONVERT(VARCHAR(5) , GETDATE() , 108) AS HourMinute ,
CONVERT(TIME,GETDATE()) AS HourMinuteSecondNanoSec --for Sql Server 2008
[/code]

Get Time in Hour:Minute - 12 Hours Format
[code]
SELECT SUBSTRING(CONVERT(varchar, getdate(), 100), 13, 2) + ':'
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 16, 2) + ' '
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 18, 2) AS MyTime
[/code]

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

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

Get Date Time
[code]
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]
[/code]

Happy Coding :)

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/

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/

Get All Stored Procedures, Tables : Database Comparision

[code language="sql"]
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
[/code]

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]