You can use ISDATE() function with some careful considerations.

SELECT CASE ISDATE(Col1) WHEN 1 THEN CAST(Col1 AS DATETIME) ELSE '2008-12-31' END

FROM Table1

SELECTp.RoutineName,

'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec]

FROM(

SELECTOBJECT_NAME(so.ID) AS RoutineName,

(SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR...

Choose DESIGN next time instead of EDIT and the designer will rearrange your query in SQL Server 2005.

DECLARE@dt1 DATETIME,

@dt2 DATETIME

SELECT@dt1 = '20080115',

@dt2 = '20080308'

SELECTDATEADD(MONTH, DATEDIFF(MONTH, '19000101', DATEADD(MONTH, Number, @dt1)), '19000131')

FROMmaster..spt_values

WHEREType = 'P'

AND Number <= DATEDIFF(MONTH, @dt1, @dt2)

So the query work now?

IF is procedural statement.

SELECTCASE TransactionType

WHEN 'REFUND' THEN CAST(-Amount AS MONEY)

WHEN 'CHARGE' THEN CAST(Amount AS MONEY)

ELSE CAST(0 AS MONEY)

END AS [Amount],

TransactionType,

TransactionStatus,

TransactionResult,

...

Oh... I see now.

You want to have an extra space after weekday name.

And you should be using Style 109 instead of 9 only, if you want century along with date.

Unless double quote is part of the column information, such as

12" Wrench Stainless steel

DECLARE@Source INT

SET@Source = 20070101

SELECT CONVERT(CHAR(10), CAST(STR(@Source, 8, 0) AS DATETIME), 111)

Can't you get rid of double quote characters when using BULK INSERT

by using a format file?

SELECT CONVERT(CHAR(10), STR(Col1, 8, 0), 111)

FROM Table1

For alternative solutions, see

http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx

-- Prepare sample data

DECLARE@Sample TABLE

(

Descr VARCHAR(20),

Value INT,

PRIMARY KEY CLUSTERED

(

Descr,

Value

),

Seq INT

)

INSERT@Sample

(

Descr,

Value

)

SELECT'Test1', 1 UNION ALL

SELECT'Test1', 2 UNION ALL

SELECT'Test1', 3 UNION ALL

SELECT'Test1', 7 UNION ALL

SELECT'Test1', ...

Interesting.

This is the resultset from above suggestion.

DescrValueDescrValue

Test11Test12

Test12Test13

Test13NULLNULL

Test17Test18

Test18Test19

Test19NULLNULL

Test112Test113

Test113NULLNULL

Test24Test25

Test25Test26

Test26NULLNULL

Test310Test311

Test311NULLNULLUsing this test codeDECLARE@Sample TABLE (Descr VARCHAR(20), Value INT)

INSERT@Sample

SELECT'Test1', 1 UNION ALL

SELECT'Test1', 2 UNION ALL

SELECT'Test1', 3 UNION ALL

SELECT'Test1', 7 UNION...

Ddin't

select datename(dw,@date) + ', ' + convert(varchar, @date, 107)

work for you?

SELECT * FROM Users

WHERE UserName LIKE '%[^a-z0-9 .]%'