How to get only DATE portion out of DATETIME column in MSSQL ?

cast(gerdate() as date)

, DATEADD(dd, 0, DATEDIFF(dd, 0, EffectiveAsOfDate)) as EffectiveAsOfDate2

, CONVERT(varchar(8), EffectiveAsOfDate, 112) as EffectiveAsOfDate3

, cast(cast((EffectiveAsOfDate - 0.500000038580247) as int) as datetime) as EffectiveAsOfDate4

 , CONVERT(CHAR(10),GETDATE(),103) as EffectiveAsOfDate5

ALTER table syntax

ALTER TABLE [dbo].[PendingTravel]

     ALTER COLUMN 
[createdate] [datetime] NULL CONSTRAINT [DF_PendingTravel_createdate] DEFAULT (getdate()),

--##############################################

ALTER TABLE [dbo].[tblEvaluationComments]

     ALTER COLUMN CommentText varchar(2000) NULL;

ALTER TABLE dbo.tblEvaluationOverview

     ADD

     [EffDateTEST] [datetime] NULL

ALTER TABLE dbo.tblEvaluationOverview

     DROP COLUMN [EffDateTEST]

--##########################################

USE [Evaluation]

GO

/****** Object:  Table [dbo].[tblRoles]    Script Date: 03/23/2010 13:46:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

ALTER TABLE [dbo].[tblMetrics]

     ADD

     [CategoryID] [int] NULL ,

       [ContractID] [int] NULL ,

       [SDSTypeID] [int] NULL    

GO

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[tblMetrics]  WITH CHECK ADD  CONSTRAINT [FK_tblMetrics_tblCategories] FOREIGN KEY([CategoryID])

REFERENCES [dbo].[tblCategories] ([CategoryID])

GO

ALTER TABLE [dbo].[tblMetrics]  WITH CHECK ADD  CONSTRAINT [FK_tblMetrics_tblContracts] FOREIGN KEY([ContractID])

REFERENCES [dbo].[tblContracts] ([ContractID])

GO

ALTER TABLE [dbo].[tblMetrics]  WITH CHECK ADD  CONSTRAINT [FK_tblMetrics_tblContracts] FOREIGN KEY([ContractID])

REFERENCES [dbo].[tblContracts] ([ContractID])

GO

--#######################################################

USE [Evaluation]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblSDSType](

       [SDSTypeID] [int] NOT NULL,

       [DSDTypeName] [varchar(20)] NOT NULL    

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblTaskBudget]  WITH CHECK ADD  CONSTRAINT [FK_tblTaskBudget_tblTasks] FOREIGN KEY([TaskID])

REFERENCES [dbo].[tblTasks] ([TaskID])

GO

T-SQL removing CHAR(10) and CHAR(13)

--SELECT REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

declare @NewLine char(2)

set @NewLine=char(13)+char(10)

update tblMetrics

     set MetricThreshold = Replace(MetricThreshold , @NewLine,'')

WHERE MetricThreshold like '%' +@NewLine +'%'

     AND MetricID = 999999

SQL Server Shrink File

use dbname;

GO

DBCC SHRINKFILE (dbname_log, 1);

GO

EXCEL :: MS-Excel Print Preview displays the size of postage stamp

Q:   Excel Print Preview shows up the size of a postage stamp

A:   change your default printer.  If you only have a single printer installed, then install another and make it the default.  View the print preview using the new printer.  Then, you can change the default printer back to the original.

Change CSS dynamically in javascript

// this simply appends the following words

//document.getElementById("message").className += "MyClass";

                                               

// this removes the words and sets border and background to White.

document.getElementById("message").className = "message";

document.getElementById("message").innerHTML = ' ';

Re-establish Identity column in MS-SQL Server (RESEED)

USE dbname
GO
DBCC CHECKIDENT (tablename, RESEED, 0)
GO

ColdFusion elimination of double quotes

<cfset mylist = Replace(Form.chkNumberList, '#chr(44)#', "'#chr(44)#'", "All")>

                                                               

<CFQUERY name="qryMarkAsPaid" datasource="#Session.DSN#">

     UPDATE dbo.table

     SET status = 99

       , PaidDate = GetDate()

     WHERE myNumber IN ('#REReplace(mylist, "''", "'", "ALL")#')

</CFQUERY>

T-SQL JOIN Update

SQL Server

UPDATE bh 

SET bh.EndDate = t.EndDate -- SELECT *

FROM Tasks t INNER JOIN BilletHours bh 

     ON bh.Contract = t.Contract 

     AND bh.TaskNumber = t.TaskNumber

     AND bh.Subtask = t.Subtask

WHERE t.Contract = '99'

     AND t.TaskNumber IN ('IND','H','PTO')

MS-Access

UPDATE _Tbl_Comments_Import x INNER JOIN dbo_tbl_Comments_Import tci ON tci.ID = x.ID
SET x.CommentCategory = [tci].[CommentCategory]
, x.CommentReviewed = [tci].[CommentReviewed]
, x.Impactful = [tci].[Impactful]
, x.Scrubbed = [tci].[Scrubbed]
, x.Sentiment = [tci].[Sentiment]
, x.Key_Phrase_I = [tci].[Key_Phrase_I]
, x.Key_Phrase_II = [tci].[Key_Phrase_II]
, x.Key_Phrase_III = [tci].[Key_Phrase_III]
, x.Scrubbed_Comment = [tci].[Scrubbed_Comment];

UPDATE [*Tbl_Demographics_Import] x INNER JOIN dbo_tbl_Demographics_Import tci ON tci.respid = x.respid
SET x.USMarket = [tci].[USMarket]
WHERE x.USMarket = 0 

SharePoint (WSS) Portal Email Merge

This is used to copy email addresses from a WSS portal and insert them into another database using a cursor.

use SP_Content_db

SELECT right(tp_Login,9) as 'login'

     , tp_Title

     , tp_Email as 'EmailAddress'    

     , tp_ExternalTokenLastUpdated

     , e.EmployeeID, CurrentEmployee

     , e.firstname, e.lastname

     , TMSv4.dbo.fn_EmployeeName(e.EmployeeID) as 'EmployeeName'

INTO #SharePoint

FROM SP_Content_db.dbo.UserInfo ui RIGHT OUTER JOIN Employees e ON right(ui.tp_Login,9) COLLATE DATABASE_DEFAULT =e.EmployeeID

WHERE tp_email COLLATE DATABASE_DEFAULT NOT LIKE 'webmaster%'

     AND tp_login COLLATE DATABASE_DEFAULT NOT LIKE '%.%'

     AND Right(tp_Login,10) COLLATE DATABASE_DEFAULT LIKE ':%'

--     AND currentemployee = 1

DECLARE @EmployeeID varchar(9)

DECLARE @EmailAddress varchar(100)

-- ########################################################################

DECLARE myCursor CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT EmployeeID, EmailAddress

FROM #SharePoint

OPEN myCursor

     FETCH NEXT FROM myCursor

     INTO @EmployeeID, @EmailAddress

    

     WHILE @@FETCH_STATUS = 0

     BEGIN    

         

          UPDATE TMSv4.dbo.Employees

          SET EmailAddress = @EmailAddress

          WHERE EmployeeID = @EmployeeID

                                     

          -- Get the next record.

          FETCH NEXT FROM myCursor

          INTO @EmployeeID, @EmailAddress

     END

CLOSE myCursor

DEALLOCATE myCursor

drop table #SharePoint