[web] SQL Server: need help writing simple query

So you want IsDate?

Also, why do you want to look at the non-dates "first" in a seperate query? If you need to have the query act differently depending on if the varchar has a valid date, you could just use a CASE in the query, rather then pulling out a seperate query and manually going through records (sorting/filtering/etc on data should be done outside of SQL as a very last resort). For example if you need to find all records with date later then Jan 1, 2001, but want invalid dates to be treated as Jan 1, 1980, you would go: WHERE (CASE ISDATE(some_datetime_field) WHEN 1 THEN CAST(some_datetime_field AS DateTime) ELSE '1/1/1980' END) > '1/1/2001'

Do not, ever, store dates in a varchar.

Change the column to a datetime - beware though that in MSSQL, changing the column of a type is very tricky (hint: you probably have to rename the table, create a new one, and copy all the data)

Mark

Quote:

Original post by markr Change the column to a datetime - beware though that in MSSQL, changing the column of a type is very tricky (hint: you probably have to rename the table, create a new one, and copy all the data)

Can you not just use alter table [mytable] alter column [my_date_column] datetime ?

Quote:

Original post by evolutional Can you not just use alter table [mytable] alter column [my_date_column] datetime ?

Not smart, you will probably lose all of the data you have in the changed column because the varchar will not convert to datetime (SQL Server has no way of knowing what crap you have in the varchar so it will not try)

Last time I checked, mssql's ALTER TABLE could not change columns' types, rename columns or add columns anywhere except at the end. This makes it very limited.

The MSSQL GUI manager "Enterprise manager" doesn't even *try* to use ALTER TABLE when you alter a table, rather it creates a complicated query batch which renames the old table, creates a new table (with the columns you want), and loads all the data across.

MSSQL's ALTER TABLE is much less flexible than the MySQL one, which can do pretty much anything.

Mark

Step 1: Find the guy that stored date/time info in a varchar.
Step 2: Hand him a box to put his things in.
Step 3: Call security to escort him out of the building.

Actually, I think OP's just trying to find the invalid records. If thats the case...

SELECT *
FROM MyTable
WHERE IsDate(MyDate) = 0

will return all records with invalid dates and...

SELECT *
FROM MyTable
WHERE IsDate(MyDate) = 1

will return the valid ones (where MyDate is a varchar field).

Hope this helps.