EDIT: Lynn, now you were quicker than me!

The problem is that the column is a DATETIME and you can't use LIKE with DATETIME values.

Use BETWEEN or a combination of <...

Here comes a Tally table solution (I just did a cut-and-paste of Jeff:s example and added the required commas in the beginning and end of the strings - the code...

No need to do the JOIN:ing in the last step in Vijays solution.

Alternative and more scalable solution (in terms of number of columns):

DECLARE @T TABLE

(ID INT IDENTITY, NAME NVARCHAR(200));

INSERT...

Hi,

My guess is that when the query uses variables for the dates, the Optimizer must assume that they can have any value. But when you hard code the dates, the...

Hi,

SQL Server can't insert into two tables at the same time.

The best solution is to let your query insert it's result into a temporary table and then use the temporary...

Hi Rhett

Since you didn't provide a very clear description of what you want I can only answer in general and show an example, to get a more detailed answer, please...

I suggest you read about how to do grouping and sorting i SQL...

Nice work Michael,

That's a simple and beautiful solution!

/Markus

This thread is about a similar problem. Perhaps some of the suggested solutions there can be of any help...

http://www.sqlservercentral.com/Forums/Topic879842-338-1.aspx

/Markus

I also found this Blog article, where the same problem is solved both by FOR XML together with XSLT and by a recursive CTE together with SQL CLR:

http://consultingblogs.emc.com/christianwade/archive/2006/09/20/SQL-Server-Standard-_2D00_-Recursive-Hierarchies-to-XML.aspx

/Markus

I tried another approach - building the XML "manually" and then cast it to the XML-type:

-- Create some test data

create table #x

(

...

Another alternative:

DECLARE @Sample

TABLE (

account INTEGER NOT NULL,

...

declare @x varchar(40)

declare @d datetime

set @x = 'Mon Jan 05 13:54:54 2009'

set @d = substring(@x, 5, 20)

select convert(char(19), @d, 120)

/Markus

Use a tally-table:

Create Table #Dim_Child

(DW_Child_ID int, DOB DateTime)

INSERT INTO #Dim_child

SELECT 1 AS DW_Child_ID, '1993-03-13 00:00:00.000'AS DOB

UNION

SELECT 2 AS DW_Child_ID,'1988-05-11 00:00:00.000' AS DOB

UNION

SELECT 3 AS DW_Child_ID, '2000-07-30 00:00:00.000' AS DOB

--=====...

Hi Martin,

If you have the value as a string in a variable, this should do it:

declare @date varchar(20)

set @date = 'SBR9D28.0178'

--set @date = 'SBR11L28.0178'

declare @year int

declare @month int

declare @yearlength tinyint

--...