Re: [SQLDownUnder] DATE QUERY

[matthew_wills]

Susan,

Maybe show us your final query and we can give you some feedback on it.

Seeya
Matthew Wills | Senior Analyst Programmer | Adviser Tools and Services|
Financial Planning and Third Party | NAB Technology | Wealth Management
Australia





|---------+---------------------------->
|         |           Susan            |
|         |           <bluesea18@yahoo.|
|         |           com>             |
|         |                            |
|         |                            |
|---------+---------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx                                                          |
  |       cc:       (bcc: Matthew Wills/AU1/WealthMgmt)                                                          |
  |       Subject:  Re: [SQLDownUnder] DATE QUERY                                                                |
  >--------------------------------------------------------------------------------------------------------------|




Thanks Matthew,
I am using Martin's and twist it around

matthew_wills@xxxxxxxxxx wrote:
 Susan,

 FundCovFrom1 >= CONVERT(varchar,@FDateFr1,103)
 AND
 FundCovTo1 <= CONVERT(varchar,@FDateTo2,103)

 Don't do code like above. Assuming FundCovFrom1 and FundCovTo1 are
 datetime
 in the database, the above query won't work on all installations of SQL
 Server (for example, if the date format is set to MDY).

 There is no need for the convert in the WHERE clause.

 Thanks
 Matthew Wills | Senior Analyst Programmer | Adviser Tools and Services|
 Financial Planning and Third Party | NAB Technology | Wealth Management
 Australia





 |---------+--------------------------------------->
 | | Susan |
 | | Sent by: |
 | | SQLDownUnderList@listserver.|
 | | readify.net |
 | | |
 | | |
 | | 20/12/2006 02:59 PM |
 | | Please respond to |
 | | SQLDownUnder |
 | | |
 |---------+--------------------------------------->
 >--------------------------------------------------------------------------------------------------------------|

 | |
 | To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx |
 | cc: (bcc: Matthew Wills/AU1/WealthMgmt) |
 | Subject: Re: [SQLDownUnder] DATE QUERY |
 >--------------------------------------------------------------------------------------------------------------|





 Thanks guys...
 I think I solve the prob.

 thanks again for your advice and help.

 Sudhir Chawla wrote:
 Hi Susan,

 The query you are using is incorrect. You have 2 dates (Funding From &
 Funding To). You wish to extract all data based on the dates entered
 by the user. Suppose user enters data as follows:

 Funding from:Jan 01 2005
 Funding To : Dec 31 2005

 The query should be:

 FundCovFrom1 >= CONVERT(varchar,@FDateFr1,103)
 AND
 FundCovTo1 <= CONVERT(varchar,@FDateTo2,103)

 This will give all the records whose Funding is starting from Jan 01
 2005 and ending on Dec 31 2005.

 Hope I have understood your question rightly & answered it correctly.

 Thanks & Regards,

 Sudhir

 On 12/20/06, matthew_wills@xxxxxxxxxx wrote:
 > Susan,
 >
 > Maybe I am missing something, but why not just:
 >
 > FundCovFrom1 = @FundFr AND FundCovTo1 @FundTo
 >
 > It would be more helpful if you showed the CREATE TABLE statement for
 the
 > table you are querying, plus some sample data in the table, plus what
 > output you want from your query (based on the sample data).
 >
 > Also, do the dates have time components (eg 1:31 PM) or not?
 >
 > Thanks
 > Matthew Wills | Senior Analyst Programmer | Adviser Tools and Services|
 > Financial Planning and Third Party | NAB Technology | Wealth Management
 > Australia
 >
 >
 >
 >
 >
 > |---------+--------------------------------------->
 > | | Susan |
 > | | Sent by: |
 > | | SQLDownUnderList@listserver.|
 > | | readify.net |
 > | | |
 > | | |
 > | | 20/12/2006 10:30 AM |
 > | | Please respond to |
 > | | SQLDownUnder |
 > | | |
 > |---------+--------------------------------------->
 >
 >--------------------------------------------------------------------------------------------------------------|


 > | |
 > | To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx |
 > | cc: (bcc: Matthew Wills/AU1/WealthMgmt) |
 > | Subject: [SQLDownUnder] DATE QUERY |
 >
 >--------------------------------------------------------------------------------------------------------------|


 >
 >
 >
 >
 > Hello everyone,
 >
 > I have a task to create a report so when the user fills date funding
 from
 > and funding to, all data between the date will be appeared. It is much
 > simpler if in the table there is only 1 field call ' funding' so I can
 use
 > ' between' function
 > however this is a bit tricky because in the table it has 2 fields call -
 > funding from and funding to both of them are datetime.
 >
 > If I use the query below, the result doesn't really what we want.
 >
 > (FundCovFrom1 between CONVERT(varchar,@FDateFr1,103)AND
 > CONVERT(varchar,@FDateFr2,103))
 > AND
 > (FundCovTo1 between CONVERT(varchar,@FDateTo1,103)AND
 > CONVERT(varchar,@FDateTo2,103))
 >
 > we want to have a result as: when we enter FundFr=18/09/2006 and
 > FundTo=18/03/2007
 >
 > ClientNo FundFr FundTo
 > 70878 18/09/2006 18/03/2007
 > 78945 18/09/2006 18/03/2007
 > 74531 18/09/2006 18/03/2007
 > any idea how do I do it?
 >
 >
 > lastly----------- I wish you all
 >
 > MERRY CHRISTMAS AND HAPPY NEW YEAR
 >
 > thanks,
 > Susan
 >






______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________


to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

This e-mail is sent by or on behalf of the named sender identified above.
If:

(a) you do not wish to receive any e-mail marketing material from this
person in the future, please forward the contents of this email to
unsubscribe@xxxxxxxxxx with the word "unsubscribe" in the
subject box.

(b) you wish to unsubscribe from all central e-mail marketing lists
used by our business, please forward the contents of this e-mail to
unsubscribeall@xxxxxxxxxx with the message "unsubscribe
from all central e-mail marketing lists" in the subject box.

If you do not forward the contents of this e-mail with your
unsubscription then it may not be able to be implemented.

The information contained in this e-mail communication may be
confidential. You should only read, disclose, re-transmit, copy,
distribute, act in reliance on or commercialise the information if you
are authorised to do so. If you are not the intended recipient of this
e-mail communication, please immediately notify us by e-mail to
postmaster@xxxxxxxxxx, or reply by e-mail direct to the sender and then
destroy any electronic and paper copy of this message. Any views
expressed in this e-mail communication are those of the individual
sender, except where the sender specifically states them to be the views
of a member of the National Australia Bank Group of companies. Any
advice contained in this e-mail has been prepared without taking into
account your objectives, financial situation or needs. Before acting on
any advice in this e-mail, National Australia Bank Limited recommends
that you consider whether it is appropriate for your circumstances. If
this e-mail contains reference to any financial products, the National
recommends you consider the Product Disclosure Statement (PDS) or other
disclosure document before making any decisions regarding any products.
The National Australia Bank Group of companies does not represent,
warrant or guarantee that the integrity of this communication has been
maintained nor that the communication is free of errors, virus or
interference.