Image Date Types

Hi,
Can any one tell me how to change an existing
Image column in a table to binary type. I am looking at
the T-SQL .
Thanks and Regards,
SajeevCREATE TABLE Tmp_myImageTable ( imagefield binary(1000) )
GO
EXEC ('INSERT INTO Tmp_testimage (im)
SELECT CONVERT(binary(1000), imagefield) FROM myImageTable ')
GO
DROP TABLE myImageTable
GO
EXECUTE sp_rename N'Tmp_myImageTable', N'myImagetable', 'OBJECT'
GO
Nathan H.O.

IIF statment in an SQL statment.

I am trying to convert a logical and date fields into number fields. I am
using IIF() but I can't seem to get the syntax correct. Help Please.
SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
FROM Order2
GROUP BY MagazineName
The overall goal is to
1) count then number of records
2) count how many are renewal's
3) count how many are canceled
example:
CHILD 50 5 1
Scott BurkeHey Scott,
Use the CASE statement in SQL, not IIF. Like this:
SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag =false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
1 END as CLRFL
FROM Order2
GROUP BY MagazineName
Michael C
"Scott Burke" wrote:
> I am trying to convert a logical and date fields into number fields. I am
> using IIF() but I can't seem to get the syntax correct. Help Please.
> SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> FROM Order2
> GROUP BY MagazineName
> The overall goal is to
> 1) count then number of records
> 2) count how many are renewal's
> 3) count how many are canceled
> example:
> CHILD 50 5 1
> Scott Burke|||Hi Michael. Thanks for the suggestion. It worked exactly the way I wonted to.
The case statement looks a lot like an IIF() statement to me.
Time to do some research.
Thanks again.
Scott Burke
"Michael C" wrote:
> Hey Scott,
> Use the CASE statement in SQL, not IIF. Like this:
>
> SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag => false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
> 1 END as CLRFL
> FROM Order2
> GROUP BY MagazineName
>
> Michael C
> "Scott Burke" wrote:
> > I am trying to convert a logical and date fields into number fields. I am
> > using IIF() but I can't seem to get the syntax correct. Help Please.
> >
> > SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> > 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> > FROM Order2
> > GROUP BY MagazineName
> >
> > The overall goal is to
> > 1) count then number of records
> > 2) count how many are renewal's
> > 3) count how many are canceled
> >
> > example:
> > CHILD 50 5 1
> >
> > Scott Burke

IIF Statement with Date checking

Hello, I am using the following to try to find date ranges and do some work.
Here is the statement
=iif(fields!mbr_join_date.value = "7/01/2003", mbt_code, "Not Grandfathered")
I have looked at the query result and I see alot of dates that are this date
but it's not putting the MBT_Code in for the value. Anyone have any
suggestions. Should I convert the Date in the Query to a different format
currently it shows as yyyy-mm-dd 00:00:00 should I trim off the time or
convert it to a string value?
Thank you in advanceScrocker,
I'm not 100% sure if your doing this in the SQL or in a cell on the
report. If its in the report you need to write it like:
iif(fields!mbr_join_date.value = "7/01/2003", FIELDS!mbt_cod.VALUE, "Not
Grandfathered")
You can't reference the field name in a control without usin gthe FIELDS!
collection.
If its in your SQL, well, IIF's dont' work, so I'm thinking your not doing
it, but if you are then use a CASE statement
CASE WHEN mbr_join_date = '7/01/2003' THEN mbr_code ELSE 'Not Grandfathered'
END as mbr_code
Michael C
"scrocker" wrote:
> Hello, I am using the following to try to find date ranges and do some work.
> Here is the statement
> =iif(fields!mbr_join_date.value = "7/01/2003", mbt_code, "Not Grandfathered")
> I have looked at the query result and I see alot of dates that are this date
> but it's not putting the MBT_Code in for the value. Anyone have any
> suggestions. Should I convert the Date in the Query to a different format
> currently it shows as yyyy-mm-dd 00:00:00 should I trim off the time or
> convert it to a string value?
> Thank you in advance
>|||Thanks Michael, I will give that a shot, Yes it's in the report not in SQL
Michael C wrote:
>Scrocker,
> I'm not 100% sure if your doing this in the SQL or in a cell on the
>report. If its in the report you need to write it like:
>iif(fields!mbr_join_date.value = "7/01/2003", FIELDS!mbt_cod.VALUE, "Not
>Grandfathered")
>You can't reference the field name in a control without usin gthe FIELDS!
>collection.
>If its in your SQL, well, IIF's dont' work, so I'm thinking your not doing
>it, but if you are then use a CASE statement
>CASE WHEN mbr_join_date = '7/01/2003' THEN mbr_code ELSE 'Not Grandfathered'
>END as mbr_code
>Michael C
>> Hello, I am using the following to try to find date ranges and do some work.
>> Here is the statement
>[quoted text clipped - 7 lines]
>> convert it to a string value?
>> Thank you in advance|||You might try:
=iif(fields!mbr_join_date.value < "7/02/2003", mbt_code, "Not
Grandfathered")
Adding one day to the date value - you might have to specify the date
as
"2003-07-02 00:00:00" (yyyy-mm-dd 00:00:00 format)
to ensure you are setting the date/time to the very start of the
following day.

iif problem brings back #Error

I'm pulling a "Number (8)" data type field for a date (formatted YYYYMMDD) from a Oracle 9i database. When the value is inserted into the database it is set a zero (A non-null database for the most part), otherwise it is for example 20061224. If the date field has a value the date is displayed correctly, but if it is zero then I get the "#Error" message.

Here is one of many iif expressions I've tried.

=iif(Len(CStr(Fields!DTE_MAILED.Value)) = 8,

((CStr(Fields!DTE_MAILED.Value)).Substring(4,2) + "/" + Right(CStr(Fields!DTE_MAILED.Value), 2) + "/" + Left(CStr(Fields!DTE_MAILED.Value), 4))

,

Nothing)

Now I've returned the value with out any formatting done to the string, and it will return "0"(zero) or a number. I've returned the lengths of the returning value and it comes back "1" or "8". I read a lot of previous posts and I thought at first that it was because I was trying to do a substring function on the zero value getting a index error. So I've changed the iif test condition many different ways with no prevail. I read a previous post where someone ended up doing his work in his SQL, but I would like to find out how to do this in the report. This is a simple expression, so I feel like there is something obvious I don't know maybe something with the format mask.

All help will be appreciated!

hi nwyork,

i don't have Oracle db so i can't try.

but as far as the error that i've encountered,

e.g. iif (condition, true exp, false exp)

you might want to put your true exp and false exp returning the same data types...

if your true exp has CStr then the false exp should put CStr

|||

Thanks for replying,

I thought that might be it so I tried returning (""), and after reading your post I tried using CStr function in the false part but I still get the error. I ran out of time so I used SubStr, Decode, and Length functions in the SQL.

|||

Hi nwyork

The first problem im seeing is in the boolean check.
You are using 2 expressions on the field.....(Cstr and Len)
If the field containes a null value there will be problems as
Len(Null) = #error

Why not try restructuring your iif statement as follows:

=iif( Fields!DTE_MAILED.Value = Nothing
,0
,((CStr(Fields!DTE_MAILED.Value)).Substring(4,2) + "/" + Right(CStr(Fields!DTE_MAILED.Value), 2) + "/" + Left(CStr(Fields!DTE_MAILED.Value), 4)))

I'm not sure if this is specific to your datasource as i've never used Oracle before but it should still do
the trick. You can even add a second iif in the false part of the expression to make sure that the length is 8
,because as soon as the expression hits false part you know that there is no
null's and you should not get an error when trying to use expressions on the the field value.

If I was unclear in any way, be sure to point it out.

It may be 2 late now but it may help when a similar problem pops up in the future ;P

G

Ignoring time stamp in my date parameter

Hi,

I'm pretty new at this, writing SQL and reporting services. I created a report with a date parameter. I need the report to ignore the timestamp. My @.Startdate is fine because the timestamps is at 12:00:00AM but my @.EndDate also has this timestamp. I need to pull all the data up to the end date the user enters without taking the timestamp into consideration.

If someone can help me out with, I would greatly appreciate it.

Thanks,

Hello,

If I understand correctly, you want to include the date that your user selects in your results. The problem is that when a parameter is used, it assumes midnight, so any values that are on that day but have a time other than midnight will not be included. In order to fix this, just add one day to your EndDate parameter.

In your SQL query, add this:

... where DateField >= @.StartDate and DateField < dateadd(d, 1, @.EndDate)

Hope this helps.

Jarret

|||

You got it! I've been adding one day when I enter the dates when I test the report. I don't know why I did not think of just adding one day in the code. I guess I was thinking too hard trying to completely ignore the timestamp.

Anyways, I think this will work with the users. THey will definitely be happy that they do not have to type in the timestamp when they run their reports.

Thank you so much!!

|||

Glad I could help!

Jarret