Comparing dates

[selectsplat]

Greetings,

Ok, I am a complete newb to vb.net (or any other ms language), but I've got 10+ years in shell, SQL, perl and php. So, I can handle technical responses to my question.

I'm building an SSiS package in SQL Server 2005, and I have need to build a script in it in VB.net.

I've gotten everything I need to work, but I'm having some trouble comparing some dates. From what I understand, I'm supposed to be able to use datediff.

So, I'm trying to compare the value stored in a system datetime variable against date.now. Here's the snippet from my code...

DateDiff("d", Date.Now(), Dts.Variables("last_run_oracle").Value) <= 1

I get various input data type errors, when I run this.

Just so you know I've thought this through, I've considered the data types of all of the parts of this expression.

First, I understand that the result from the datediff is a string and I've evaluating it against a number. So, that's probably a problem there.
Second, the 'last_run_oracle' variable is a datetime.
I'm not sure what date.now is, but I assume it would be a datetime as well.
And the date part I'm returning is a string, I konw, so it might be that I can't compare two dates adn return a string.

Any help you can provide would be greatly appreciated.

 

[JohnH]

First, I understand that the result from the datediff is a string and I've evaluating it against a number. So, that's probably a problem there.

No, documentation says the return value is type Long.

Second, the 'last_run_oracle' variable is a datetime

It may be, but documentation states Value property type is Object, commonly used when property may return values of any type, so convert/cast it to Date type using the CDate function.

I'm not sure what date.now is, but I assume it would be a datetime as well.

It is a Date value, this is the VB primitive data type, which by .Net CLR is represented with the DateTime structure.

You can also subtract two Date values with the regular - substraction operator, the return value is a TimeSpan value, from which you can for example access the Days property; (date1-date2).Days

 

[selectsplat]

Well, I specifically set the ValueType property in SSiS for the last_run_oracle variable as 'DateTime'. But I tried to convert it anyways.

When I try to convert the variable using CDate like this...

Dim last_run_dt As DateTime = CDate(Dts.Variables("last_run_oracle").Value)

I get the error...

Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.String.System.IConvertible.ToInt32(IFormatProvider provider)
at System.Convert.ToInt32(Object value)
at ScriptTask_bb3774fabc6d4ac7a023da9e5830b65d.ScriptMain.Main() in dts://Scripts/ScriptTask_bb3774fabc6d4ac7a023da9e5830b65d/ScriptMain:line 42

 

[JohnH]

I don't know about SSiS, but obviously it is returning a string, so you should have a look at that and options for converting it to Date. Date.Parse or ParseExact may be options, possibly specifying some culture the date string is formatted as, if it is not possible to get a Date value from SSiS that is.

 

[Budius]

Could you give us a debug.print(Dts.Variables("last_run_oracle").Value)?? It helps to see the value the system is trying to compare.
I've personally never used datetime or date types (as VB almost seamlessly understand string types as dates, although it probably is bad programming from me), but most times I had problems with them was usually just a matter of throwing a Format(variable, "dd//MMyyyy hh:mm:ss") on both sides and voilá.

 

[selectsplat]

Could you give us a debug.print(Dts.Variables("last_run_oracle").Value)?? It helps to see the value the system is trying to compare.
I've personally never used datetime or date types (as VB almost seamlessly understand string types as dates, although it probably is bad programming from me), but most times I had problems with them was usually just a matter of throwing a Format(variable, "dd//MMyyyy hh:mm:ss") on both sides and voilá.

So, you're saying that it might be easier to make sure both sides are string rather than making sure both sides are date?

That would work, I'm sure, as long as I can get the difference between both 'strings' in number of days.

 

[Budius]

So, you're saying that it might be easier to make sure both sides are string rather than making sure both sides are date?

All I'm saying is that I've never used dates types before (and it might well be bad programming from my part), but as long as I made sure to use a Format(var, "dd/MM/yyyy hh:mm:ss") on both strings before using the date.diff... it works!

Just always remember that the 'month' in the format string have to be upper case 'MM' and that MS-SQL 2005 (and newer) uses "yyyy/MM/dd hh:mm:ss" as it standard format, regardless of culture.

 

[selectsplat]

To be sure I'm clear, can you provide an example of how you would convert the current date to a string with a format, and the variable (ts.Variables("last_run_oracle").Value) into a strong with the same format and compare them?

 

[Budius]

i'm not in the office anymore, so it's not spot on exact. But when I did date comparisons, it was something like that:

Dim number as Integer = _
                Date.Diff("d", Format(now, "dd/MM/yyyy hh:mm:ss"), _
                Format(ts.Variables("last_run_oracle").Value, "dd/MM/yyyy hh:mm:ss"))

if you're still having problem, insert a:

MessageBox.Show(ts.Variables("last_run_oracle").Value)

on your code, and let me know what is in the message.