Feeds:
Posts
Comments

Posts Tagged ‘Convert’

declare @t table ( f1 int identity , f2 varchar(100))
insert into @tselect 'hello'unionselect 'world'

select * from @t 

-- Table to XML 
select  ( select 1 as f1r1 , 2 as f1r2 for XML path ('f1'), type ),
 ( select 'hello' as f2r1 , 'world' as f2r2 for XML path ('f2'), type )for XML path (''), root('sample')

-- XML to table declare @myXML xml
set @myXML = '<sample>  <f1>    <f1r1>1</f1r1>    <f1r2>2</f1r2>  </f1>  <f2>    <f2r1>hello</f2r1>    <f2r2>world</f2r2>  </f2></sample>'

select  a.b.value('f1[1]/f1r1[1]','int') as f1 , a.b.value('f1[1]/f1r2[1]','int') as f2
 , a.b.value('f2[1]/f2r1[1]','varchar(10)') as f3 , a.b.value('f2[1]/f2r2[1]','varchar(10)') as f4from @myXML.nodes('sample')a(b) --read this as @myXML.nodes('[root]') [table name]([column name])
Advertisements

Read Full Post »


-- 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

Read Full Post »


-- How to convert n number of columns to just two:

--Problem: There were n number of unknown columns with known data Types. And two definite columns lets call them col 1 and col 2 of which I knew the name

--I just needed one of the unknown column and one definite column 

	
		declare @FinalWithOnlyTotal table
		(
			serial int identity, -- if sorting is required 
			unpivotedcolumn varchar(20),
			Total float
		)	
		if (select count(1) from #ResultsGrouped2) = 1 -- When just one new type is selected. Also tells me the number of unknown columns returned  
		begin 
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
		end 
		else
		begin
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
		end
		
		-- The below produces data for the graph 
		select	unpivotedcolumn as [Label],
				Total as [Results] 
		from @FinalWithOnlyTotal
		where unpivotedcolumn  <> 'Total'




Read Full Post »

-- Finding out the the start and end dates of the given month and year
DECLARE @Month INT, @Year INT

SELECT @Month = 1, @Year = 2011

------ ======= Initialize ==============
DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = convert(DATETIME, '1/' + str(@Month, 2) + '/' + str(@Year, 4), 103) -- 103 = dd/mm/yyyy
SET @EndDate = DATEADD(ms, - 3, DATEADD(m, DATEDIFF(m, 0, @StartDate) + 1, 0))

---------===========================
SELECT @StartDate, @EndDate
/*
SSRS

Parameters!DateFrom.Value =CDate(Str(Parameters!prmYear.Value) + "-" + Str(Parameters!prmMonthFrom.Value) + "-1") -- Beginning of the month (with a given year and month number)

Parameters!DateTo.Value =DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01", Parameters!DateFrom.Value)+1, "1900-01-01")) -- End of the month


-- End of a a different selected month

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01",

CDate(Str(Parameters!prmYear.Value) + "-" + Str(Parameters!prmMonthTo.Value) + "-1") -- >> This is similar to DateFrom but instead prmMonthTo is used

)+1, "1900-01-01")) -- End of the month


*/

Read Full Post »

-- TSQL -- Fractions (or float) not being returnedTSQL -- Counting Nulls »TSQL, SSRS -- Date Manipulation
--July 19, 2010 by Usama 

SELECT getdate() Date1, getdate() + 1 Date2
     , DATEDIFF(day,GETDATE() + 1, getdate()) NegetiveFigure
	, DATEDIFF(day,GETDATE(), getdate() + 1) PositiveFigure_PlaceTheBiggerOnTheRight

Select dateadd(D, 0, datediff(D, 0, GETDATE())) -- Time Stripped (without any data type conversion for optimized performance)

Select DATEADD(m, DATEDIFF(m,0,GETDATE() )-1, 0) -- Beginning of previous month
select DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) , 0)) -- End of previous month

/*
SSRS 

=DateAdd("m",DateDiff("m", "1900-01-01″, Today()) -1, "1900-01-01″) -- Beginning of previous month

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01″, Today()), "1900-01-01″)) -- End of previous month
*/

-----------------------------------------------------------------

Select DATEADD(m, DATEDIFF(m,0,GETDATE() ), 0) -- Beginning of the month

select DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) + 1, 0)) -- End of the month 

/*
SSRS 

=DateAdd("m",DateDiff("m", "1900-01-01″, Today()), "1900-01-01″) -- Beginning of the month 

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01″, Today())+1, "1900-01-01″)) -- End of the month
*/ 

------------------------

-- Converting String to Datetime

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 '


-------------------------------------- 

select 
DATENAME(weekday , getdate())
,  DATEADD(ms,-3,DATEADD(D, DATEDIFF(D,0,GETDATE()) , 0)) -- End of play > Yesterday 
, DATEADD(D, DATEDIFF(D,0,GETDATE())-1 , 0) -- Beginning of play > Yesterday 

Read Full Post »

Use the conversion at your discretion.

A where clause having the convert keyword to convert date to varchar and compare would decrease the query time tremendously and you might need to do that when you want to split the time from date.

To test, I once ran a query containing a number of convertions and it ran for over 8hrs.
Amusingly, the same query ran for about 2.5 hours without the conversion.

--Example of such a query with the convert keyword:
select a, b
from tbl nolock
where convert(varchar(10), dateField, 121) &gt;= '2009-08-10'

--Optimizated/Alternative method 
declare @myDate datetime 
set @myDate = dateadd(day, 0 ,datediff(day,0, getdate()))
Select @myDate 

Similar Topics:
Find the end of month –
http://wp.me/pAchH-2t)

Read Full Post »