Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
beancounter
Starting Member
5 Posts |
 Posted - 2011-12-07 : 14:13:20
|
| I have customer records where the account # remains the same but the two digit occupant code changes with each new customer at the same service address. I am trying to get a list of just the "active" customers who are identified by nothing in the end_date field. For some reason my, where end_date is Null is not working and I am getting closed and active accounts regardless.Any suggestions are greatly appreciated,JohnHere is the statement:Select pu_account.Account_no,RIGHT('0'+CONVERT(VARCHAR,pu_account.occupant_code),2) AS NUM,'"'+rtrim(name)+'"','"'+convert(varchar(8),serv_street_no)+' '+rtrim(serv_street)+'"' as Street, serv_city,serv_province,serv_postal_zip, '$'+convert(varchar(12),convert(money,Balance)), Convert(varchar(4),DatePart(yyyy,getDate()))+'-'+ convert(varchar(2),DatePart(mm,getDate()))+'-'+ convert(varchar(2),DatePart(d,getDate()))+' '+ convert(varchar(2),DatePart(hh,getDate()))+':'+ convert(varchar(2),DatePart(mi,getDate())) as ReportDate from pu_account,Puaccbal,pu_account_pay where pu_account_pay.end_date is Null and pu_account.account_no = Puaccbal.account_no and pu_account.occupant_code = Puaccbal.occupant_code and pu_account.account_no = pu_account_pay.account_no |
|
|
beancounter
Starting Member
5 Posts |
Posted - 2011-12-07 : 18:35:56
|
| How about this? Does anyone know how to do a "where end_date is not less than today" I don't know the correct syntax if anyone could provide?Thanks,John |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-08 : 01:21:02
|
| where pu_account_pay.end_date >=dateadd(dd,datediff(dd,0,getdate()),0)will take all records having end_date as today or later------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
beancounter
Starting Member
5 Posts |
Posted - 2011-12-08 : 15:08:47
|
| Thanks for the reply. I tried it and I didn't get any reocrds. I think the problem is the end_date field is blank. What I am trying to retrieve is only the records with a blank/null in the end_date field. For some reason Is Null doesn't work either.Thanks again for the reply I appreciate it,John |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-12-08 : 15:25:09
|
| What's the data type of end_date? |
|
|
|
beancounter
Starting Member
5 Posts |
Posted - 2011-12-08 : 18:57:19
|
| Russell,when the field is populated it is date time (7/8/2008 0:00)John |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-12-08 : 19:37:06
|
| Need to know the data type. Seems maybe you're storing dates/times in a varchar field?If you have "blank" fields that aren't NULL, then you're definitely storing dates in character type fields.If that's the case, try comparing to empty string.WHERE end_date = '' |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-09 : 00:19:53
|
quote:
Originally posted by beancounter
Russell,when the field is populated it is date time (7/8/2008 0:00)John
i dont think type is datetime thenmake sure you use proper datatype for columns------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Where "field" is Null not working?