better method for DTS: field

Showing posts with label field. Show all posts

Friday, March 30, 2012

Is using varchar() as index a bad idea?

Hi ,
I have an application whose data is stored in some other db and I am
converting
to SQL. The existing app has a table, Contractors with a field ContractorCo
de
char(10) as an index to the table. For some reason, I am against using
recognizable data as an index an instead opt for GUID. I s having a char or
varchar
field as an index slower than a GUID field index?
ThanksOpa
I personally try to avoid creating an index on GUID . ( I try keep my index
as small as possible) Having index on VARCHAR/CHAR depends on your
specific requirements . See an execution plan of the query, is an optimizer
used the index and then make a decision?
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||There is a lot of debate on using GUIDs as primary keys. Here is an example:
http://www.sql-server-performance.c...erformance.asp. There is no
problem in using CHAR(10) as your index as long as the column helps you in
identifying the entity uniquely.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:584774
Index on GUID will take up more storage therefore bigger index therefore
more I/0. This would be pronounced on a clustered index.
The Contarctors table with Contractor code, what is the variety of data
within the col ? and what types of searches (if any) will be commited
on that column?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uS4EcQjMGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Opa
> I personally try to avoid creating an index on GUID . ( I try keep my
index
> as small as possible) Having index on VARCHAR/CHAR depends on your
> specific requirements . See an execution plan of the query, is an
optimizer
> used the index and then make a decision?
>
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
char
>|||Opa (Opa@.discussions.microsoft.com) writes:
> I have an application whose data is stored in some other db and I am
> converting to SQL. The existing app has a table, Contractors with a
> field ContractorCode char(10) as an index to the table. For some
> reason, I am against using recognizable data as an index an instead opt
> for GUID. I s having a char or varchar field as an index slower than a
> GUID field index?
The last question is not really meaningful, because there are always a lot
of "it depends".
But generally, as a guid is 16 bytes, it's longer than the code, and the
longer the field, the less keys you get on a page, and the bigger the
index gets, and the more pages to read.
I would suspect, though, that the Contractors table is not of a size
where this is a much of an issue. Then again, the code may be used as an
FK in a larger table where it may matter.
Personally, I rather use the code as key, than GUID which is much more
difficult to manage. If you want artificial keys, integer is probably
better.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ok so if I have varchar(16) vs GUID will this index field always perform bet
ter
or equal to GUID is SELECTS, INSERTS, JOINS etc?
BTW, I disagree with your comment stating the column should help identify
the entity uniquely in a visual way.
"SriSamp" wrote:

> There is a lot of debate on using GUIDs as primary keys. Here is an exampl
e:
> http://www.sql-server-performance.c...erformance.asp. There is no
> problem in using CHAR(10) as your index as long as the column helps you in
> identifying the entity uniquely.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
>
>|||The performance aspect is something that you need to check for. Regarding my
other comment, what I meant was, if the CHAR(10) field is the "natural" key
for your table, you should go ahead and use it, rather than defining another
key.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:824B3723-E44D-410F-930F-58A4F31F5DB1@.microsoft.com...
> Ok so if I have varchar(16) vs GUID will this index field always perform
> better
> or equal to GUID is SELECTS, INSERTS, JOINS etc?
> BTW, I disagree with your comment stating the column should help identify
> the entity uniquely in a visual way.
>
> "SriSamp" wrote:
>|||Have a look at this excellent article from Kimberly L Tripp
http://www.sqlskills.com/blogs/kimb...
f-a290db645159
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||You, sir, are a moron. I usually don't say that about people, but I'll
make an exception in your case.
Stu|||When you use "index", I'm assuming you really mean to say "primary key". A
primary key is a type of index, but an index is not a primary key.
I don't believe there is any basic performance benefit to using integer
based keys over char based keys. As far as SQL Server is concerned, a key is
just a block of bytes, and the smaller the better. A GUID is typically
displayed as a string of text, but is stored internally as a 16 byte
integer. Consider instead an int (4 bytes) or smallint (2 bytes) identity.
The only use I can see for using GUID (globally unique) keys is if the
Contractor data is to be merged with Contractor data from another system and
you want to retain the same surrogate key values. However, this could be
better implemented by adding a SystemCode to the Contractors table and have
SystemCode + ContractorCode as the primary key.
Perhaps keeping ContractorCode as the primary key would be the best solution
unless there is a strong and compelling reason to implement a surrogate key.
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks

is too long. Maximum length is 128. Error

I try to Update a field of a table using this statement

UPDATE Table SET field="Forget......(long text)" WHERE id=1

and I get this error

The identifier that starts with 'Forget your busexcursions. Marta Patiño takes a trip out of this world at LaLaguna's Science Museum.In April 2001, De' is too long. Maximum length is 128.

What is wrong?

Looks like the "field" column in your table is defined to hold a maximum of 128 characters of data. Update your table definition to make the column bigger or reduce the size of your data.

Bill

|||Literal text is enclosed in single quotes. Identifiers (Like column names) may be enclosed in double quotes. Because you've put the text in double quotes, it is saying that your column name (That whole block of text) is too long.|||

If you use a t-sql statement like

Update Models Set LocalDescription = "General Description" Where ModelId = 2

And if you have LocalDescription and "General Description" as the name of the fields you will update one field with orders value.

"" points to an identifier (a field name) is up to 128 characters.

If you are trying to set a field with a value more than it is expecting you will get "string or binary data would be truncated" error message.

Eralper

http://www.kodyaz.com

Wednesday, March 28, 2012

Is this query possible?

I want to sum up the amt field where the acct or group is A1000. Since
A1110 belongs to group A1100, and A1100 belongs to group A1000 it
should pick those amts up as well. The total should be 600. I have
the sql below but I can't figure how to pick up acct A1110 since it
should be rolling up to A1000 because of A1100.

select sum(amt) from tbl_bal
where acct='A1000' or group='A1000';

tbl_bal
acctgroupamt
A1000A1000100
A1100A1000200
A1110A1100300I think this violates some of the basic principles of relational model
(not sure which ones). However I would love to see some sort of
solution to this problem though. There is some type of process to break
this into normal table with right groupings.

mouac01@.yahoo.com wrote:

Quote:

Originally Posted by

|||mouac01@.yahoo.com wrote:

Quote:

Originally Posted by

I would agree with othell...@.yahoo.com about the design...at least its
not clear.
In SQL Server 2005 this approach might work depending on the tables
design.

WITH Balance(Acct, amt)
AS
(
SELECT [group], amt FROM tbl_bal WHERE acct = 'A1000'
UNION ALL
SELECT G.acct , G.amt FROM tbl_bal G
INNER JOIN Balance B
ON G.[group] = B.acct WHERE G.acct <'A1000'
)
SELECT SUM(amt) FROM Balance;|||Would need a few more rows to make sure but from what you have given
you need to do a google search on -- recursive SQL -- web is better.
It is not fun or pretty but it is rather common and far better example
exists then I could copy/paste here.

Monday, March 26, 2012

is this possible? ...not an SQL master

Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
RickAn example:

INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable

Assumes that the character is always the last position and length of 1.

Originally posted by entangled
Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
Rick|||HI,

try to use decode(substr(backPM,length(backPM)-1,1),'R',substr(backPM,1,length(backPM)-1,backPM)

and use the same formula for AheadPM column

Originally posted by entangled
Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
Rick|||Thank you for the example. This one example pretty much addresses both issues. I will work with this and see how I can apply this approach.

many thanks,
Rick Sperling

Originally posted by dmmac
An example:

INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable

Assumes that the character is always the last position and length of 1.

is this possible?

I would like to display agregated data, as well as my image, but it seems that this requires me to put my image file field in my group by statement too, but sql does not seem to allow sorting of images. Is what I am trying to possible? Or do I have to do both things separate?

here is the code:

select tbltimesheets.weekno, sum(tblentries.hrsnorm) as hrsnorm, sum(tblentries.hrspot) as hrspot, sum(tblentries.hrsnpot) as hrsnpot, tblusers.username, images.imagefile from
tbltimesheets
inner join tblentries on tbltimesheets.tskey = tblEntries.TSKey
inner join tblusers on tbltimesheets.userkey = tblusers.userkey
inner join images on images.userkey = tblusers.userkey
group by tbltimesheets.weekno, tblusers.username, images.imagefileYou have to ddo the thing separate. YOu can use some smart SQL for this, though, working, and still do it in one query.

Or, instead summing, you could use another method (like one returning the first image in the group).

Is this possible......??

Say I have a table with one field and there are 26 records in it... A, B, C, D, E, ... etc.

If I do a select statement on that table I get 26 records, one for each field. (select fieldName from tblName)

Is it possible to set a variable equal to a single string off of the select statement and delimit it with a chosen delimiter (ie "A,B,C,D,E,F......")

Thank You for the help!!!With which DBMS? There is no standard SQL answer to this.|||Using MS SQL 7|||Looking for something like

set @.stringName = (select fieldName & ',' from tblName)

So that @.stringName is set to a string 'A,B,C,D,E,....'|||What about this?

drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str|||Originally posted by snail
What about this?

drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset

set @.str = @.str + (select fieldName from tblName)

something like that where my recordset can be turned into a string.|||Originally posted by gman_gsxr750
That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset

set @.str = @.str + (select fieldName from tblName)

something like that where my recordset can be turned into a string.

Just try and you'll see...|||Originally posted by snail
Just try and you'll see... Holy moley!!! I've never seen that before!!!

Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

One last question (only because I've never used the code in that way before...

can I put a conditional on it

set @.str = @.str + code from table (where id < 100)

or something like that?

And did I mention..... Thank you!|||Originally posted by gman_gsxr750
Holy moley!!! I've never seen that before!!!

Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

One last question (only because I've never used the code in that way before...

can I put a conditional on it

set @.str = @.str + code from table (where id < 100)

or something like that?

And did I mention..... Thank you!

Why not?|||Originally posted by snail
Why not? Ever get that rush when something finally goes your way and things work out?

Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.

Do you know what that kind of query is called so I can reference?|||Originally posted by gman_gsxr750
Ever get that rush when something finally goes your way and things work out?

Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.

Do you know what that kind of query is called so I can reference?

I have no idea...|||Originally posted by snail
I have no idea... OK, last question, hopefully you can help me with this.

Here's my code:
declare @.idpeople int
set @.idpeople = 200002
declare @.str varchar(8000)
set @.str = ''

select @.str = @.str + ',' + ideventcode from tblPeopleEvents where idpeople = @.idpeople

let's say this returns the following ',CXL,AS' (two codes CXL and AS)

this works fine, but if I add an order by clause to it (so it returns AS,CXL instead), I only get one of the two values (CXL)

any ideas on how far I can take the select portion (where, order by, group by, etc)?|||it's called a magic query

it works, and it produces the result by magic

hey snail, where's the comma between values?

;)|||OK, here's my final code. I used a subquery to get the result set the way I needed it. Much thanks to Snail for the help. And to r937 for the sarcasm ;).

create procedure spGetEventString
@.idpeople int,
@.eventString varchar(255) OUTPUT

as

set @.eventString = ''

-- Create string of Event codes
-- use sub query to order result set
select @.eventString = @.eventString + rTPE.ideventcode
from
(
select top 100 idEventCode + ',' as idEventCode
from tblPeopleEvents
where idpeople = @.idpeople
order by idEventCode
) as rTPE

--Remove Trailing Comma
set @.eventString = left(@.eventString,len(@.eventString)-1)

return|||Originally posted by r937
it's called a magic query

it works, and it produces the result by magic

hey snail, where's the comma between values?

;)

I am not a magician I am only learning... ;)|||I think it should be called the Loophole query, because it doesn't look like it should work, but it does.|||Originally posted by blindman
I think it should be called the Loophole query, because it doesn't look like it should work, but it does.

That is a bit harsh ...

http://www.dbforums.com/showthread.php?threadid=979593|||Originally posted by Enigma
That is a bit harsh ...

http://www.dbforums.com/showthread.php?threadid=979593 Yep, that's it. Works like a charm too!|||This type of query is the coolest thing I've learned from DBForums, but I haven't seen any Microsoft Documentation that talks about it. That's why it seems like a loophole to me.

Does anybody know of any BOL or MS Support references regarding this self-referential query?|||Thats what it should be called -->"A self-referential query"|||and it even does what Enigma was asking in the referenced post:

select @.str=@.str+case @.str when '' then '' else ',' end + code from test

Is this possible without a cursor?

I have something like

update table

set field = ...

where field = ...

and for each entry that was effected by this query I want to insert an entry into another table.

I have always done this with cursors is there a more effecient way? For some reason cursors run a lot slower on my sql2005 server than the sql2000 server...

im not sure if this is what you want but maybe something like this... well first of all you cant do two updates on two tables in the same query so you would have to do it in a sproc (am i right?)

so it would be like

updated table2

set field = "my child"

where field in (select field1

from table1

where field = "something")

update table 1

set field ="my parent"

where field = "something"

is that right? sorry i dont know the answer off the top of my head just trying to help

|||

I believe you are looking for the output clause of the update statement:

USE AdventureWorks;GODECLARE @.MyTableVar table( EmpID int NOT NULL, OldVacationHours int, NewVacationHours int, ModifiedDate datetime);UPDATE TOP (10) HumanResources.EmployeeSET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.EmployeeID, DELETED.VacationHours, INSERTED.VacationHours, INSERTED.ModifiedDateINTO @.MyTableVar;--Display the result set of the table variable.SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDateFROM @.MyTableVar;GO--Display the result set of the table.--Note that ModifiedDate reflects the value generated by an--AFTER UPDATE trigger.SELECT TOP (10) EmployeeID, VacationHours, ModifiedDateFROM HumanResources.Employee;GO

Monday, March 12, 2012

Is there such a thing as creating an unbound report with Crystal Reports?

I am using Crystal Reports in the .NET 2005 environment. Let's say I want to create a simple one page report (like a memo) and I want one field on the report (this is not exactly what I need to do but this example addresses the concept I am looking for). Let's say I have no database and my data is in a string variable. how the heck can I dynamically set the value of the field on the report to that variable without implementing some crazy pseudo-binding strategy?

To me, it seems as though this is the most basic report possible and yet from all of my reading and searching it seems that this cannot be done with some simple syntax such as:

ReportSource.ReportObjects("Field1").Text = stringVariable;

Did Crystal leave that most basic and fundamental property out of their data model for a reason?

Should I look at alternative reporting products that are less database centric?

I used to use DataDynamics ActiveReports and it worked just like that above, however I would prefer to use Crystal as it is more industry standard.

Any help would be appeciated. Thanks.

-KevinI always love it when people answer their own questions! Well here is the answer to mine. I found it indirectly on another thread! Go dev-archive!

JournalMemo journal = new JournalMemo();
journal.DataDefinition.FormulaFields["UnboundString1"].Text = "\"It Worked\"";

That is easy enough although it would have been more intuitive if I could have done that from the ReportObjects collection.

JournalMemo is a Crystal Report that I created with the designer in the IDE. The actual JournalMemo.cs class is created by Crystal Reports when creating an embedded report in the designer and it inherits the ReportClass class.

Is there query I can write to remove empty tags from a xml field?

I want to remove empty tags:

from an xml type field in a table. Can I do it using a query and a modify?

Use the modify method and the XQuery delete instruction as follows (uses an XML variable but can of course also be done with a column of type XML):

Code Snippet

DECLARE @.xml xml;

SET @.xml = '<root>

<name/>

</element>

</root>';

SET @.xml.modify('

delete //*[not(node())]

');

SELECT @.xml;

|||

PERFECT!!! I am thrilled. Easy and simple and solved my problem.

Is there no longer the need to refresh in SP1

I use RS on and off and I have noticed that when I move a text field in a
report and preview it the report doesn't automatically refresh data anymore.
I'm I seeing things or is this the case, if it is then good on you MSYes, Preview caches data in SP1 (you can turn it off if needed).
See also:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_report_designer_preview
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John" <John@.discussions.microsoft.com> wrote in message
news:BEA4051E-D622-4448-8D54-891F37C333C4@.microsoft.com...
> I use RS on and off and I have noticed that when I move a text field in a
> report and preview it the report doesn't automatically refresh data
anymore.
> I'm I seeing things or is this the case, if it is then good on you MS
>

Monday, February 20, 2012

Is there any easy way to evaluate complex date logic in expressions?

Hello all,

I am new to SSIS, so I am hoping there is an easier way to do this...

I need to evaluate a date in a field and determine if it is between the beginning and end of whatever the current month is... In Access, this was written as something like:

IIF(datevalue >= CDate(Format(Now(),"mm/01/yy")) AND datevalue < CDate(Format(DateAdd("m",1,Now()), "mm/01/yy)), value1, value2)

Trying to recreate this in SSIS using expressions during a derived transformation has been extremely difficult. Here is what I came up with:

(DUE_DATE >= (DT_DATE)( (DT_WSTR,2)MONTH(GETDATE())+"/01/"+ (DT_WSTR,2)YEAR(GETDATE()))) && (DUE_DATE<(DT_DATE)( (DT_WSTR,2)MONTH( DATEADD("m",1,GETDATE()) )+"/01/"+(DT_WSTR,2)YEAR( DATEADD("m",1,GETDATE() )))) ? value1 : value2

Any help you all could give would be appreciated.

Thanks!

Josh

I've just given this a go with a column called [OrderDate] (which comes from a SalesOrderHeader in AdventureWorks). The following works fine for me:

OrderDate < GETDATE() ? "Y" : "N"

Can you not do that? Why are you trying to parse out the different parts of GETDATE()?

-Jamie

|||This expression will determine if the DUE_DATE is in the current month, as defined by GETDATE().

YEAR(GETDATE()) + MONTH(GETDATE()) == YEAR(DUE_DATE) + MONTH(DUE_DATE) ? "value1" : "value2"

|||

jaegd wrote:
This expression will determine if the DUE_DATE is in the current month, as defined by GETDATE().

YEAR(GETDATE()) + MONTH(GETDATE()) == YEAR(DUE_DATE) + MONTH(DUE_DATE) ? "value1" : "value2"

I would suggest using :

(YEAR(GETDATE())*100) + MONTH(GETDATE()) == (YEAR(DUE_DATE)*100) + MONTH(DUE_DATE) ? "value1" : "value2"

That will result in 200612 for Dec 2006.

|||

Hi Jaimie,

Thanks for the advice. However, I need to be able to tell if the due_date is between the 1st and last day of the currernt (or any given) month. I think your suggestion would return true for all orders with a due_date less than today... which would exclude orders due in the remainder of the month and would include orders due prior to the beginning of the month.

Best regards,

Joshua

|||

Hi Tom,

Thanks for the help! I think that will do what I'm looking for and will be a lot easier...