Feeds:
Posts
Comments

Posts Tagged ‘look’


-- New Table Structure 
CREATE TABLE dbo.FirstTable(
	FirstField varchar(100) null
) 


--Table Rename -- 
exec sp_rename 'dbo.FirstTable', 'dbo.TableRenamed'

-- Columns Rename 
exec sp_rename  'dbo.FirstTable.FirstField', 'FieldRenamed'


/* as a note, instead of using sp_rename to rename COLUMNS an alternative method is to use the ALTER table command to change a column's data type or (to re-name it)a new column can be added and old one dropped */

alter table dbo.TableRenamed 
add FieldRenamedSecondTime varchar(100) NULL

alter table dbo.TableRenamed 
drop column FieldRenamed varchar(100) NULL

-- To just change the datatype:
alter table dbo.TableRenamed 
alter column FieldRenamed int NULL

Advertisements

Read Full Post »

--- Just a note ---
SELECT @@Datefirst -- By Default this is 7 which denotes Sunday and the values for Monday is 1 whihc cat be set if needed like below:
 -- set Datefirst 1
 ---------------

-- With Datepart we can retrieve the integer value of any part of a datetime value:
--    year, quarter, month, day of the year, day, week number, weekday number, hour, minute, second, or millisecond.
-- with datename we can retrieve its name / string value
-- Example:
SELECT datepart(week, getdate()) WeekNumber
 ,datepart(weekday, getdate()) WeekDayNumber
 ,datepart(month, getdate()) MonthDayNumber

--Week does not have a name so its sting value is returned
SELECT datename(week, getdate()) WeekName
 ,datename(weekday, getdate()) WeekDayName
 ,datename(Month, getdate()) MonthDayName

Result:

Read Full Post »

SELECT DAY(DATEADD (m, 1, DATEADD (d, 1 – DAY(GETDATE()), GETDATE())) – 1)

/*
1.    Find the current day of the month (let’s call it x)
2.    Subtract x from 1. This should give us a negative number (let’s call it y). Go back y days in the current month.
3.    This will give the 1st day in the month (let’s call it z)
4.    Add 1 month to z. This gives the first day of the next month. Subtract 1 from it and this gives us the last day of the current month.
5.    And that’s what we want!!
*/

Read Full Post »

-- SQL Server 2000/2005 (or 2008)
SELECT convert(CHAR(12), GETDATE(), 114) -- This is more widely used

SELECT DATEADD(Day, 0 - DATEDIFF(Day, 0, GetDate()), GetDate()) -- Retaining DateTime format

-- SQL Server 2008
SELECT CONVERT(TIME, GETDATE()) AS HourMinuteSecond
	,CONVERT(DATE, GETDATE(), 101) AS DateOnly

Read Full Post »

--Check if a table exists (and drop it if does) before creating it again
IF EXISTS (
  SELECT NAME
  FROM anydbnameOptional.dbo.sysobjects -- use TYPE to specify tables, Sps etc
  WHERE type = 'u' -- <a href="http://msdn.microsoft.com/en-us/library/ms177596.aspx">http://msdn.microsoft.com/en-us/library/ms177596.aspx</a>
   AND NAME = 'whatever'
  )
 DROP TABLE whatever

--Check if a database exists
IF EXISTS (
  SELECT NAME
  FROM master..sysdatabases
  WHERE NAME = 'DatabaseName'
  )
 SELECT 'ok'

DECLARE @anotherWay VARCHAR(100)

-- if select is used then the outer bracket must be used as well
--set @anotherWay = (select DB_NAME())
SET @anotherWay = DB_NAME()

PRINT @anotherWay

--=====================
-- To check and delete an object
--if object_id ('dbo.udf_CapitaAnsweredCallsKPI8') is not null
--    drop function dbo.udf_CapitaAnsweredCallsKPI8
--go
----=========================
-------- =========== Check if a column exists  ================================ --------------------------
IF EXISTS (
  SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'myTableName'
   AND COLUMN_NAME = 'myColumnName'
  )
 ---- The below way (sys.columns) is depricated
 IF EXISTS (
   SELECT *
   FROM sys.columns
   WHERE NAME = N'columnName'
    AND Object_ID = Object_ID(N'tableName')
   )
 BEGIN
  -- Column Exists
 END
   ----------------------====================================================----------------------------------

Read Full Post »

-- Converting String to Datetime
-- The STR function converts any stringto numeric. The length passed to it helps it to tell how it should truncate.
-- so if str is of 2 becomes [space]2
DECLARE @SelectedMonth INT = 1
 ,@year INT = 2010
-- Replace is not needed in the below line but is just illustrated if you want a leading 0 when the month is a single digit
DECLARE @date DATETIME = convert(DATETIME, '28/' + replace(str(@SelectedMonth, 2), ' ', '0') + '/' + str(@year, 4), 103)

SELECT @date

Read Full Post »

DECLARE @passed VARCHAR(max)

SET @passed = 'FirstBit exec dbo.usp_Sites 4'

PRINT substring(@passed, 1, -- Change 1 to the vlaue you want the extraction to start from
		patindex('% exec %', @passed) - 1 -- Chage " exec " to any value in the string till which you want it extracted
	)
PRINT substring(@passed, patindex('% exec %', @passed) + 1, len(@passed) - patindex('% exec %', @passed))

-- The function Left() and Right() are also used when you want the extraction to start from either
-- left most or right most side.
-- To extract certain characters from a string using charindex
DECLARE @find AS VARCHAR(max)

SET @find = 'dbo'

PRINT substring(@passed, charindex(@find, @passed), len(@find))
-- Patindex could also have been used above but ii used the format &gt;&gt; '% [this is where your txet goes]  %' )
---Difference in patindex() and charindex()
PRINT patindex('%' + @find + '%', @passed)
PRINT charindex(@find, @passed)


Read Full Post »