using …

Pure C#

Archive for the ‘SQL’ Category

http://www.mssqltips.com web casts

Posted by esersahin on 05/03/2012


http://www.mssqltips.com/webcastlist.asp

Posted in SQL, Sql Server | Leave a Comment »

Dynamic creation of Insert, Update, Delete Stored procedures

Posted by esersahin on 09/07/2010


http://www.sqlservercentral.com/articles/Stored+Procedures/67055/

Posted in SQL, Sql Server, Stored Procedure | 1 Comment »

SQL TIP: Auto Increment in an UPDATE statement.

Posted by esersahin on 11/04/2010


http://haacked.com/archive/2004/02/28/sql-auto-increment.aspx

Posted in AutoIncrement, SQL, Sql Server, Transact-SQL, Update | Leave a Comment »

Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

Posted by esersahin on 15/09/2009


http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Posted in SQL, Sql Server, Temporary Table, Transact-SQL | Leave a Comment »

SQL Server 2005 Row Versioning-Based Transaction Isolation

Posted by esersahin on 17/05/2009


http://msdn.microsoft.com/en-us/library/ms345124.aspx

Posted in SQL, Sql Server | Leave a Comment »

Fastest Way To Increment An ID Column

Posted by esersahin on 03/04/2009


http://www.sqlservercentral.com/scripts/IDENTITY/65999/

Posted in SQL, Sql Server, Transact-SQL | Leave a Comment »

Using the OUTPUT clause with INSERT,UPDATE, DELETE

Posted by esersahin on 20/03/2009


http://www.java2s.com/Tutorial/SQLServer/0040__Insert-Delete-Update/UsingtheOUTPUTclausewithINSERTUPDATEDELETE.htm

Posted in Output, SQL, Sql Server, Transact-SQL | Leave a Comment »

SQL Server / T-SQL Tutorial

Posted by esersahin on 20/03/2009


http://www.java2s.com/Tutorial/SQLServer/CatalogSQLServer.htm

Posted in Cast, Common Table Expressions, Cross-Tabs, CTE, Functions, Instead Of, Pivot Tables, Raiserror, ROW_NUMBER, SQL, Sql Server, Sql Server Error, Sql Server Join, SQLCLR, Stored Procedure, Transact-SQL, Trigger, UNION, UNION ALL, Update with Join | Leave a Comment »

SQL Server / T-SQL

Posted by esersahin on 20/03/2009


http://www.java2s.com/Code/SQLServer/CatalogSQLServer.htm

Posted in Cast, Cross-Tabs, CTE, Functions, Pivot Tables, SQL, Sql Server, Sql Server Error, Sql Server Join, Stored Procedure, Transact-SQL, Trigger | Leave a Comment »

Convert DateTime

Posted by esersahin on 13/03/2009


CREATE PROCEDURE sproc_UpdateTemp_Sub
(
@PreviousDay datetime,
@PresentDay datetime
)
AS

select * from table1 where date between @previousday and @presentday

Go

CREATE PROCEDURE sproc_UpdateTemp
AS

DECLARE @PreviousDay datetime
DECLARE @PresentDay datetime

select @PresentDay = convert( datetime, convert( varchar(10), getdate(), 101 ))
select @PreviousDay = dateadd( day, -1, @PresentDay )

exec sproc_UpdateTemp_Sub @PreviousDay, @PresentDay

Go

Declare @presentday as varchar(10)
SET @PresentDay =  convert( varchar(10), getdate(), 101 )
select * from table where Convert( varchar(10), date1, 101)  = @presentday

Why are you using varchar instead of datetime datatype? If it is just to lose the time part of GetDate it is much more efficient convert it using a float.

Something like

select cast(floor(convert( float,getdate())) as datetime)

returns you today’s date with the time part set to 00:00:00.

You could create a UDT like this:

create function dbo.fnDateFromDateTime(@da

tetime datetime)
returns datetime
AS
begin
return cast(floor(convert( float,@datetime)) as datetime)
end

And call it like this:

select dbo.fnDateFromDateTime(GetDate())

Posted in Cast, Convert, DateTime, SQL, Sql Server, Transact-SQL | Leave a Comment »