Using a date function on a column that has different types

Question

Is it possible to use a function like DATEADD() or DATEPART() on a varchar column that may or may not hold something that looks like a datetime?

Currently I'm trying this by using a WHERE clause to select only rows that have datetime values in this column and getting the usual arithmetic overflow error.

I could perform some sort of operation to extract just the datetime values and put them in a temp table but I'm curious about this.

Example

SELECT DATEPART(wk, DateCreated) FROM tblOrders WHERE colType = 'datetime'

~~Don't~~ worry about the table design, it's ~~not~~ as bad as that.

Donnie · Accepted Answer · 2010-11-18 22:37:47Z

6

Don't worry about the table design, it's not as bad as that.

Seems unlikely, but...

Use IsDate

answered Nov 18, 2010 at 22:37

Donnie

47k10 gold badges67 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alberto Martinez Over a year ago

A little punch and a a proper answer in just three lines... :-)

frusDev Over a year ago

I never even thought of using IsDate(). But still, I couldn't use DATEPART() on the column with this in my WHERE clause. Ended up going the temp table route anyway because, as it turns out, the table design sucks harder than I initially realised. Also edited my post after some frustration in creating a report today.

Collectives™ on Stack Overflow

Using a date function on a column that has different types

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related