Compare 2 dates

Question

Compare 2 dates

Arjan Fraaij

Mr or Mrs. 500

Points: 574
More actions
June 7, 2008 at 4:00 am

#121887

Hello,
I want to compare to dates to now or a SQL statement needs to be executed:
I want to do something like:
date1 == date2 ? [SQL to execute when true] : [SQL to execute when false]
I only go wrong already in the first expression part.
(DT_WSTR,8) (
(YEAR(@[User::dtLastExtrationDate]) * 10000) +
(MONTH(@[User::dtLastExtrationDate]) * 100) +
DAY(@[User::dtLastExtrationDate]))
==
(DT_WSTR,8) (
(YEAR(GETDATE()) * 10000) +
(MONTH(GETDATE()) * 100) +
DAY(GETDATE()))
This is going to be something like : 20080605 == 20080607
But on evaluate expression it gives me the following error.
Cannot convert expression value to property type. Cannot convert "system.boolean" to "System.string"
I tried to find something on the net but could not find the explicit error.
Can you please give me a hint in the right direction????
Grtz,
Arjan

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 1

I don't believe you need the "@" sign. Also, what datatype does the function return? If one or both are datetime datatypes (like GETDATE() is), then you can just compare the values instead of going through all the conversions that you're trying to go through.

--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)

Arjan Fraaij Mr or Mrs. 500 Points: 574 More actions · Answer 2

Hello Jeff,

Thanks for the reply, the variable also contains a datetime data type. Extracted from a SQL table.

I do the conversion because the written datetime to the table also contains the hh:mm:ss, just like getdate() does return.

I've I compare the varDate with getdate it almost always compare to false because it takes in account the hh:mm:ss for me I only need to compare the Date thats why I do the conversion..

Or could I also use convert in Expressions? like CONVERT(char, GETDATE(), 112) to convert to YYYYMMDD but that does the same is I now did doesn't it...

The datatype that should be returned should be a boolean. The full expression is:

(DT_WSTR,8) (

(YEAR(@[User::dtLastExtrationDate]) * 10000) +

(MONTH(@[User::dtLastExtrationDate]) * 100) +

DAY(@[User::dtLastExtrationDate]))

==

(DT_WSTR,8) (

(YEAR(GETDATE()) * 10000) +

(MONTH(GETDATE()) * 100) +

DAY(GETDATE()))

?

"SELECT 1"

:

"DELETE FROM " + @[User::strExtTargetTable] + " WHERE FileID = '" + @[User::strExtFileID] + "'"

The first part of the expression must return a boolean. The expression structure is

I'm using this to fill the SQL statement in a Control Flow SQL Task.

Excuting both date expressions separatly gives for both the YYYYMMDD in string. So why does this not work???????

And now when I'm typing I think I know why!

I tried to evaluate each part separate to test but the datatype for the SQLSource Property Expression is STRING of course and testing only this first part of the total statement returns a boolean..... Stupid ME!!! is this what you did mean Jeff?

Thanks again then 🙂

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

Kind of... if both datatypes, 1 for the function and 1 for the column, are datetime, why not use that to your advantage?

DECLARE @Today DATETIME

DECLARE @Tomorrow DATETIME

SELECT @Today = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0),

@Tomorrow = @Today+1

SELECT *

FROM yourtable

WHERE (@[User::dtLastExtrationDate]) >= @Today

AND (@[User::dtLastExtrationDate]) < @Tomorrow

--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)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

Crud... I just realised this is for integration services... hope what I wrote for you still works...

--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)

Arjan Fraaij Mr or Mrs. 500 Points: 574 More actions · Answer 5

Jeff,

Indeed we are in the SSIS forum section 🙂 your last script does not work in SSIS expressions. But I understaind my problem and everything is working fine now. Thanks for the help.

Grtz Arjan