Feeds:
Posts
Comments

Posts Tagged ‘Convertion’

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

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 »

-- 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(day, datediff(day, 0, getdate()), 0) as StartDateToday,
	dateadd(ms, -3, dateadd(day, datediff(day, 0, getdate())+1, 0)) as EndDateToday

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 »