Datetime data from Excel to SQL Server 2005 by SSIS

A derived column to convert datetime data from Excel to DT_DBTimeStamp is used. Also all NULL values should be taken care too.
Expression should like this in the derived column:
 
(ISNULL(Sampling_Date) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)Sampling_Date)

Sort Max with Binary convertion way

Question: Get minimum DateC and Maximum DateR along with the value in column Amount.

DECLARE @TableA TAble (DateR Datetime, DateC datetime, Amount smallmoney)

INSERT

INTO @TableA

SELECT

’01/01/08′, ’01/05/088′, 500.00

UNION ALL

SELECT ’01/06/088′, ’01/08/088′, 400.00

UNION ALL

SELECT ’01/13/088′, ’01/18/088′, 350.00

UNION ALL

SELECT null, ’01/21/088′, 650.00

UNION ALL

SELECT ’01/21/08′, null, 350.00

 

SELECT

CAST(Substring(binCol,1,8) as Datetime) as DateRmax

,

minDateC

,

CAST(Substring(binCol,9,4) as money) as MoneyWithMaxDateR

FROM

(

SELECT MAX(CAST(DateR as binary(8))

+

CAST(Amount as binary(4)) )

as

binCol, MIN(DateC) as minDateC

FROM

@TableA

)

as t

–OR

 

SELECT

CONVERT(DateTime, Substring(binCol,1,8)) as DateRmax

,

minDateC

,

CONVERT(SmallMoney, Substring(binCol,9,4) ) as MoneyWithMaxDateR

FROM

(

SELECT MAX(CAST(DateR as binary(8))

+

CAST(Amount as binary(4)) )

as

binCol, MIN(DateC) as minDateC

FROM

@TableA

)

as t


Tip for Fisical year and month in fisical year

SELECT dtcol3, YEAR(DATEADD(m, 6, dtcol3)) AS [FiscalYear]

,

MONTH(DATEADD(m, 6, dtcol3)) AS [FiscalMonth] FROM theTable


Using UpdatePanels with Google Maps

The trick is to not wrap the whole page in one big UpdatePanel provided by Jacob Reimers. Leave ASP.NET GoogleMap control outside of any Updatepanels.
http://www.reimers.dk/blogs/jacob_reimers_weblog/archive/2008/11/23/an-example-using-updatepanels-and-google-maps.aspx
 

DateTime column default string format (US)

A datetime column's default conversion format to a string is mon dd yyyy hh:miAM (or PM).  
If you want to work with the default, the WHERE clause will look like this:
....WHERE dtColumn Like '%Sep  9 2008%' to retrieve all September 9, 2008 records
Two spaces are needed for the day part. If the day part is one digit number,
 you need to pad a space infront of it.

A Group BY GROUPING SETS sample in SQL Server 2008

Today, I came across a solution with GROUP BY SETS sample (http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/897ab93d-046a-4aa6-87de-0ad1b438b04c).
 
Table from user: tanoshimi
 Bedroom int,  

 Type int 

)  

 

DECLARE @i int = 0  

WHILE @i < 100  

BEGIN 

INSERT INTO saeed_sp VALUES 

(RAND() * 10, RAND() * 3)  

SET @i = @i +1  

END 

Solution from Artemakis:

SELECT    

isnull((CAST(Bedroom AS varchar(8)) + ‘ Bedroom’),‘Type ‘ + cast([type] as varchar(8))),  

CAST(COUNT(Bedroom) AS varchar(8)) + ‘ Items’    

FROM saeed_sp     

GROUP BY grouping sets(  

(bedroom),([type])  

)

 


TOP (100) PERCENT … ORDER BY in View or CTE

 
I use a CTE to test this TOP (100) PERCENT… ORDER BY issue.
TOP 100 PERCENT  does a table scan and finished up without doing the ORDER BY action;
By using a variable for the TOP PERCENT as TOP (@i) PERCENT or a number other than 100, it will execute the ORDER BY clause;
As Adam answered above, by using TOP aNumber …. ORDER BY, it works too.
As George pointed out, it is important to use ORDER BY on the outer-most select statement to get correct ordered result.
 
The question is from the discussion at MSDN: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/266bb93d-41dd-4d5e-ab3b-0ef4cac3b45c#page:2