Need to compare two columns

Question

Need to compare two columns

jcbally

Grasshopper

Points: 22
More actions
September 10, 2010 at 2:20 pm

#226803

I Need to compare two columns [PROP_FL_DT] < [PROP_MT_DT]
One Column is Varchar Data Type, the other is Date
the varchar data Type is mixed with Null, Date structure like '04/05/2010' and Characters 'abcdsfg'
I need to only compare the date value and leave the Null and Characters.
I tried LEN such as below and returns what I need, but what syntax can I use this in a select statement?
select PROP_MT_DT FROM N_LN_RR if LEN(PROP_MT_DT) = 10 (only date value has 10 Characters)
But I need it to go into something such as
select PROP_MT_DT FROM N_LN_RR where PROP_FL_DT < LEN(PROP_MT_DT) = 10
Thanks for any help, newbie here....:-D

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Cory E. SSCertifiable Points: 7076 More actions · Answer 1

made2lastjj (9/10/2010)
I Need to compare two columns [PROP_FL_DT] < [PROP_MT_DT]
One Column is Varchar Data Type, the other is Date
the varchar data Type is mixed with Null, Date structure like '04/05/2010' and Characters 'abcdsfg'
I need to only compare the date value and leave the Null and Characters.
I tried LEN such as below and returns what I need, but what syntax can I use this in a select statement?
select PROP_MT_DT FROM N_LN_RR if LEN(PROP_MT_DT) = 10 (only date value has 10 Characters)
But I need it to go into something such as
select PROP_MT_DT FROM N_LN_RR where PROP_FL_DT < LEN(PROP_MT_DT) = 10
Thanks for any help, newbie here....:-D

Try the ISDATE() function, http://msdn.microsoft.com/en-us/library/ms187347.aspx

-- Cory

jcbally Grasshopper Points: 22 More actions · Answer 2

The isdate would return 0 and 1 correct?

I need to be able to compare the two tables to verify date value ranges are <> one or the other.

Cory E. SSCertifiable Points: 7076 More actions · Answer 3

jcbally (9/10/2010)
The isdate would return 0 and 1 correct?
I need to be able to compare the two tables to verify date value ranges are <> one or the other.

The isdate will allow you to compare the columns...

WHERE ISDATE(yourdate) = 1 and youdate = otherdate

-- Cory

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 4

jcbally (9/10/2010)
I Need to compare two columns [PROP_FL_DT] < [PROP_MT_DT]
One Column is Varchar Data Type, the other is Date
the varchar data Type is mixed with Null, Date structure like '04/05/2010' and Characters 'abcdsfg'
I need to only compare the date value and leave the Null and Characters.
I tried LEN such as below and returns what I need, but what syntax can I use this in a select statement?
select PROP_MT_DT FROM N_LN_RR if LEN(PROP_MT_DT) = 10 (only date value has 10 Characters)
But I need it to go into something such as
select PROP_MT_DT FROM N_LN_RR where PROP_FL_DT < LEN(PROP_MT_DT) = 10
Thanks for any help, newbie here....:-D

It's impossible to answer this question until you supply further details. Crucially, we need to know if all your VARCHAR dates are stored in the same format and what that format is eg DD/MM/YYYY or MM/DD/YYYY etc

Run the code snippet below and you will see what I mean

SELECT ISDATE('04/23/2009')

SELECT ISDATE('23/04/2009')

SELECT ISDATE(CONVERT(DATETIME,'23/04/2009', 103))

jcbally Grasshopper Points: 22 More actions · Answer 5

All the date formats in the varchar column are the same

Format '04/05/2010' mm/dd/yyyy

I need to compare of greater then the other colum of those dates against another column that has a date data type.

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 6

jcbally (9/12/2010)
All the date formats in the varchar column are the same
Format '04/05/2010' mm/dd/yyyy
I need to compare of greater then the other colum of those dates against another column that has a date data type.

You could adapt this script to suit your requirements

IF NOT OBJECT_ID('tempdb.dbo.#CompareDates', 'U') IS NULL DROP TABLE #CompareDates

IF NOT OBJECT_ID('tempdb.dbo.#CompareValidDates', 'U') IS NULL DROP TABLE #CompareValidDates

CREATE TABLE #CompareDates (DateColumn datetime, VarcharColumn varchar(20))

INSERT #CompareDates

SELECT '06/23/2009', 'any old text' UNION ALL

SELECT '03/07/2007', '09/04/2006' UNION ALL

SELECT '09/01/2004', 'more old text' UNION ALL

SELECT '07/29/2005', '08/06/2005' UNION ALL

SELECT '08/16/2008', '08/19/2008'

SELECT * INTO #CompareValidDates

FROM #CompareDates

WHERE ISDATE(VarcharColumn) = 1

SELECT * FROM #CompareValidDates

WHERE DateColumn < CONVERT(DATETIME, VarcharColumn, 101)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Carefull folks... ISDATE isn't all that you believe it is...

SELECT ISDATE('2010'), CAST('2010' AS DATETIME),

ISDATE('201006'), CAST('201006' AS DATETIME),

ISDATE('June 2010'), CAST('June 2010' AS DATETIME)

Without additional checks for formatting and the like, character based columns can contain all sorts of wonderful "dates" that you aren't expecting especially if some ladies named "June" or "Jan" or "May" or "July" or "April" work there. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 8

Hopefully the code below will let you "invalid date" errors. You can't really guarantee that SQL will perform the checks in the order you want, but hopefully it will short-circuit before the last check causes an error.

WHERE ([PROP_MT_DT] LIKE '[01][0-9]/[0123][0-9]/[12][0-9][0-9][0-9]'

AND ISDATE([PROP_MT_DT]) = 1

AND [PROP_FL_DT] < CONVERT(datetime, [PROP_MT_DT], 101))

--Add a % to the like if you need it (i.e., if some mt_dt have a valid date followed by other chars).

If you still get invalid dates, you might have to use a [var]char format, like so:

WHERE ([PROP_MT_DT] LIKE '[01][0-9]/[0123][0-9]/[12][0-9][0-9][0-9]'

AND ISDATE([PROP_MT_DT]) = 1

AND CONVERT(char(8), [PROP_FL_DT], 112) < SUBSTRING([PROP_MT_DT], 5, 4) + LEFT([PROP_MT_DT], 1, 2) + SUBSTRING([PROP_MT_DT], 3, 2))

Scott Pletcher, SQL Server MVP 2008-2010

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

scott.pletcher (9/13/2010)
Hopefully the code below will let you "invalid date" errors. You can't really guarantee that SQL will perform the checks in the order you want, but hopefully it will short-circuit before the last check causes an error.
WHERE ([PROP_MT_DT] LIKE '[01][0-9]/[0123][0-9]/[12][0-9][0-9][0-9]'
AND ISDATE([PROP_MT_DT]) = 1
AND [PROP_FL_DT] < CONVERT(datetime, [PROP_MT_DT], 101))
--Add a % to the like if you need it (i.e., if some mt_dt have a valid date followed by other chars).
If you still get invalid dates, you might have to use a [var]char format, like so:
WHERE ([PROP_MT_DT] LIKE '[01][0-9]/[0123][0-9]/[12][0-9][0-9][0-9]'
AND ISDATE([PROP_MT_DT]) = 1
AND CONVERT(char(8), [PROP_FL_DT], 112) < SUBSTRING([PROP_MT_DT], 5, 4) + LEFT([PROP_MT_DT], 1, 2) + SUBSTRING([PROP_MT_DT], 3, 2))

That's a whole lot better than just an ISDATE. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcbally Grasshopper Points: 22 More actions · Answer 10

Using the isdate to a temp table seemed to

Get all the correct rows needed for date compare.

Thank to everyone for the helpful information, great site with

Very knowledgeable people.