Wouldnt it be better to have an article or Stairway series on BIML as...

We will need more information than you have provided,

Can you post the DDL Create for the table and sample data, along with expected results.

EDIT : Ideally you should not...

sqlquery29 (11/26/2014)

---

Hi Sean,

I tried writing the function

CREATE FUNCTION dbo.Convert_Date (@date_column decimal(7,0))

Returns date as begin

declare @date date

set @date = CONVERT(DATE,CASE WHEN LEFT(@date_column,1) = '1' THEN '20'

ELSE '19' END + RIGHT(@date_column,6),...

Very true, and there are so many different ways to do ETL, into a data warehouse, and a lot depends on things like whether your dimension is a Type 1...

lshanahan (11/25/2014)

---

One of the Kimball Group's books, The Microsoft Datawarehouse Toolkit deals specifically with implementation using the MS stack which will likely have good examples (I haven't read it myself,...

The Table structure is very...

Surely you can simply

DECLARE @oldDate varchar(10) = '0980423'

SELECT

DATEFROMPARTS

(

convert(Int,LEFT(@oldDate,3))+1900 --Year

,Substring(@oldDate,4,2)--Month

,Right(@oldDate,2)--Day

)

The output is a DATE datatype, this obviously assumes the data is correctly formatted and clean.

As others have said...

Gail,

To be honest people will adapt, yes it will be a shed load of work for developers.

Personally speaking, I'll probably do what I did when I heard that the ORDER...

I know we're getting a little side tracked, and you give a very good example Luis of why you shouldn't do it.

Final question from me about this, is this a...

Gail/Lynn/Ed,

I don't disagree, and as Lynn states maybe if MS enforced it so that code wouldn't compile without the use of the statement terminators, like they do in C#/C++ their...

Isnt this your Issue

INSERT INTO #TEMP_TABLE

(COL2, COL3, COL4)

SELECTCOALESCE(T1.COL2, T2.COL2),

COALESCE(T1.COL3, T2.COL3),

COALESCE(T1.COL4, T2.COL4),

@param1, @param1

FROM #TEMP1T1

FULL OUTER JOIN #TEMP2T2

ON T1.COL2 = T2.COL2

AND T1.COL3 = T2.COL3

ORDER BY COALESCE(T1.COL2, T2.COL2),

COALESCE(T1.COL3, T2.COL3),

COALESCE(T1.COL5, T2.COL5)

The...

I've just done an example with a table as its source rather than the single string.

DECLARE @t TABLE (Id smallint,Name varchar(10),CountryList varchar(100))

INSERT INTO @t

VALUES

(1, 'ABC', '00')

,(2, 'CDE', '01020304')

,(3, 'XYZ',...

Koen Verbeeck (11/13/2014)

---

The CTE should be followed by a SELECT, INSERT, UPDATE or DELETE statement, not by an IF statement.

ps: the semicolon is a statement terminator, it has no business...

You are right you don't need the subselect :w00t:, Its just a quirk I have developed in regards to using this method especially when using multi-column tables.

Using a tally table it becomes simple

DECLARE @String VARCHAR(100) = '010203040506'

;WITH Cte_Tally

AS

(

SELECT ((Row_NUMBER() OVER (ORDER BY (SELECT NULL)) *2)-1) Pos FROM sys.columns

)

SELECT

SubString(@String,Pos,2)

from CTE_Tally

Where Pos<Len(@String)

For more details search this site...