Posts Tagged ‘Convert’

I needed a way to access a shared location that prompted for user name and password adn downloaded/diaplyed a TIFF image.
I had the credentials with me but to give the users a seemless experience I had to embbed that username and password.

I tired the impersonation from the link below but it generated “An error occurred while attempting to impersonate.” No matter what I tried it did not work. It couold that there was a policy that didn’t allow impersonation.


Then from vairous different research I figures a way of just. Using the below code I could pass the username and password to access the location.

    Private Declare Auto Function LogonUser Lib "advapi32.dll" (ByVal un As String, ByVal domain As String, ByVal pw As String, ByVal LogonType As Integer, ByVal LogonProvider As Integer, ByRef Token As IntPtr) As Boolean
    Public Declare Auto Function CloseHandle Lib "kernel32.dll" (ByVal handle As IntPtr) As Boolean

  Dim tokenHandle As New IntPtr(0)

            If LogonUser("username", "domain", "password", 2, 0, tokenHandle) Then
                Dim newId As New WindowsIdentity(tokenHandle)
                Using impersonatedUser As WindowsImpersonationContext = newId.Impersonate()

			'perform impersonated commands

                End Using
                'logon failed

            End If
        Catch ex As Exception

        End Try

Now I needed to display the image in an asp.net Image control. I soon found that the control cannot read TIFF directly so I used the below technique:


But then I realised there was more restriction because copying the file over using File.Copy was locking the file until I closed the window. I realised I could may be stream the image in the browser since the files can be accessed from the IIS server directly.
I used the below technique from the user “afetchko”:


Once that was completed I discovered that a Tiff could contain more than one page. Then I found and implemented the below:


Fianlly I stiched the pages into one image usign the solution below from the user “Pascalsz”


Below is a helpful tip on how to get the library:


Read Full Post »

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 »

-- 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  
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
		-- 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

SELECT @Month = 1, @Year = 2011

------ ======= Initialize ==============

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

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

	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


=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 


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


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 –

Read Full Post »