Need to change the date in a report. Query Help!
-
I need to change the day in a date. For example, the database returns all dates needed as 09/01/06, but I need it to return all of the dates as 9/15/06 for a report. Is there a way to change this in the query?
Thanks,
Kasey
-
Well, its hard to tell you if there is a way to change this in the query if we havn't seen the query. Can you post your query and an examle of the results you are getting and the new results you want to see?
-
Sorry bout that.
Select
p.scode as PropID,
t.umonth,
'A' as Activity,
Case when a.scode IN ('14901000','14902000','14904000','14905000','14906000') then '2000'
when a.scode IN ('15101000','15102000','15104000','15105000','15106000','15109000') then '4000'
when a.scode IN ('15301000','15305000','15306000','15401000','15404000') then '1000' else null end as ADS_Life,
Case when a.scode IN ('14805000','14905000','15105000','15305000') then 'CP'
when a.scode IN ('15301000','15306000','15401000','15404000') then 'FE'
when a.scode IN ('18100100','18100150','18100160','18100170','18100200','18100300','18100400',
'18100450','18100500','18100600','18200100','18400100','18500100','18600100','18800100',
'18930100') then 'IA' when a.scode IN ('14801000','14806000','14809000') then 'LD'
when a.scode IN ('14901000','14902000','14904000','14906000') then 'LI'
when a.scode = '26406000' then 'NP' when a.scode IN ('15101000','15101893','15102000','15104000',
'15106000','15109000') then 'RB' else null end as Class,
Case when a.scode IN ('14905000','15105000','15305000','15301000','15306000','15401000','15404000',
'14901000','14902000','14904000','14906000','15101000','15101893','15102000','15104000','15106000',
'15109000') then 'MF' when a.scode IN ('18100100','18100150','18100160','18100170','18100200','18100300',
'18100400','18100450','18100500','18100600','18200100','18500100','18800100','26406000') then 'SL'
else null end as Depr_Method_Amt,
Case when a.scode IN ('14905000','15105000','15305000','15301000','15306000','15401000','15404000',
'14901000','14902000','14904000','14906000','15101000','15101893','15102000','15104000','15106000',
'15109000') then 'MF' when a.scode IN ('18100100','18100150','18100160','18100170','18100200','18100300',
'18100400','18100450','18100500','18100600','18200100','18500100','18800100','26406000') then 'SL'
else null end as Depr_Method_Book,
Case when a.scode IN ('14901000','14902000','14904000','14905000','14906000','15101000','15101893','15102000','15104000',
'15105000','15106000','15109000','15301000','15305000','15306000','15401000','15404000','18100100','18100150','18100160',
'18100170','18100200','18100300','18100400','18100450','18100500','18100600','18200100','18400100','18500100','18800100',
'18930100','26406000') then 'SL' else null end as Depr_Method_Int,
Case when a.scode IN ('14901000','14902000','14904000','14905000','14906000','15101000','15102000','15104000','15105000',
'15106000','15109000','15301000','15305000','15306000','15401000','15404000') then 'MF' when a.scode IN ('18100100','18100150',
'18100160','18100170','18100200','18100300','18100400','18100450','18100500','18100600','18200100','18500100','18800100','26406000')
then 'SL' else null end as Depr_Method_State,
Case when a.scode IN ('14901000','14902000','14904000','14905000','14906000','15101000','15102000','15104000','15105000',
'15106000','15109000','15301000','15305000','15306000','15401000','15404000') then 'MF' when a.scode IN ('18100100','18100150',
'18100160','18100170','18100200','18100300','18100400','18100450','18100500','18100600','18200100','18500100','18800100','26406000')
then 'SL' else null end as Depr_Method_Tax,
Case when a.scode IN ('15104000','15105000','15106000','15109000','15101893') then '100' when a.scode IN ('14901000','14902000','14904000',
'14905000','14906000','15101000','15301000','15305000','15306000','15401000','15404000') then '150' else null end as Depr_Percent_AMT,
Case when a.scode IN ('15104000','15105000','15106000','15109000','15101893') then '100' when a.scode IN ('14901000','14902000','14904000',
'14905000','14906000','15101000','15301000','15305000','15306000','15401000','15404000') then '150' else null end as Depr_Percent_Book,
' ' as Depr_Percent_Int,
Case when a.scode IN ('15104000','15105000','15106000','15109000','15101893') then '100' when a.scode IN ('14901000','14902000','14904000',
'14905000','14906000','15101000') then '150' when a.scode IN ('15301000','15305000','15306000','15401000','15404000') then '200' else null
end as Depr_Percent_State,
Case when a.scode IN ('15104000','15105000','15106000','15109000','15101893') then '100' when a.scode IN ('14901000','14902000','14904000',
'14905000','14906000','15101000') then '150' when a.scode IN ('15301000','15305000','15306000','15401000','15404000') then '200' else null
end as Depr_Percent_Tax,
a.sdesc as sDesc,
Case when a.scode IN ('15301000','15305000','15306000','15401000','15404000','18100100','18100150','18100160','18100170','18100200','18100300',
'18100400','18100450','18100500','18100600','18200100','18500100','18800100','26406000') then '500' when a.scode IN ('14901000','14902000',
'14904000','14905000','14906000') then '1500' when a.scode IN ('15104000','15105000','15106000','15109000','15101000','15102000') then '2706'
else null end as Est_Life_YYMM_AMT,
Case when a.scode IN ('15301000','15305000','15306000','15401000','15404000','18100100','18100150','18100160','18100170','18100200','18100300',
'18100400','18100450','18100500','18100600','18200100','18500100','18800100','26406000') then '500' when a.scode IN ('14901000','14902000',
'14904000','14905000','14906000') then '1500' when a.scode IN ('15104000','15105000','15106000','15109000','15101000','15102000') then '2706'
else null end as Est_Life_YYMM_Book6,
a.scode as Acct,
sum(CASE t.uMonth WHEN convert(datetime, '01-Sep-2006') THEN
CASE t.hAcct WHEN pm.hRetain THEN (isnull(t.sBegin,0))
ELSE isnull(t.sBegin,0) END
ELSE 0 END) BegBal,
sum(CASE sign(CASE t.hAcct WHEN pm.hRetain THEN (isnull(t.sMtd,0)) ELSE isnull(t.sMtd,0) END)
WHEN -1 THEN 0
ELSE CASE t.hAcct
WHEN pm.hRetain THEN (isnull(t.sMtd,0))
ELSE isnull(t.sMtd,0) END
END) Debit,
sum(CASE sign(CASE t.hAcct WHEN pm.hRetain THEN (isnull(t.sMtd,0)) ELSE isnull(t.sMtd,0) END)
WHEN -1 THEN
CASE t.hAcct
WHEN pm.hRetain THEN (isnull(-t.sMtd,0))
ELSE isnull(-t.sMtd,0) END
ELSE 0 END) Credit,
sum(CASE t.uMonth
WHEN convert(datetime, '01-Sep-2006') THEN
CASE t.hAcct
WHEN pm.hRetain THEN (isnull(t.sBegin,0)+isnull(t.sMtd,0))
ELSE (isnull(t.sMTD,0)+isnull(t.sBegin,0)) END
ELSE 0 END) EndBal
From param pm inner join lockout lc on lc.hchart = pm.hchart
inner join property p on lc.hprop = p.hmy
inner join acct a on p.itype = 3
inner join total t on (t.hppty=p.hmy and a.hmy=t.hacct)
Where lc.hchart = pm.hchart
and lc.hprop = p.hmy
and a.scode in (14801000,14805000,14806000,14809000,14901000,14902000,14904000,14905000,14906000,
15101000,15101893,15102000,15104000,15105000,15106000,15109000,15301000,15305000,15306000,15401000,
15404000,18200100,15500140,15500010,15500014,15500016,15500019,15500021,15500022,15500053,15500051,
15500082,15500030,15500032,15500034,15500023,15500050,15500025,15500020,15500052,15500037,15500011,
15500026,15501000,15500066,15500054,15500027,15500029,15500036,15500024,15500012,15500040,15500070,
15500080,15500038,15500069,18400100,18600100,18100160,18100170,18100200,18100300,18100400,18100450,
18100500,18100600,18500100,18800100,26406000,18930100,18100100,18100150)
and t.ibook in (1,5)
and t.uMonth between convert(datetime, '01-Sep-2006') and convert(datetime, '01-Sep-2006')
and p.hmy = 568
Group By
p.scode,
a.scode,
a.sdesc,
t.ibook,
t.umonth
-
I think this is where the problem is:
and t.uMonth between convert(datetime, '01-Sep-2006') and convert(datetime, '01-Sep-2006')
what does t.uMonth have? Is it date field? If so do u have values other than Sept-1-2006?
-
Yes, it is a date field. It is the month posted so the day is always the first, but the month and year change.
-
Is this a static query or was this generated by an application? To answer your original question, yes, all you should need to do is change your dates. Keep in mind that your Sept 1st date is in more places than just in the BETWEEN statement in your WHERE clause. If this is a static query, I would suggest making a local variable of datetime datatype and using the variable throughout your query as opposed to keeping hard coded dates in the SQL.
-
Okay, I'll try that. Thank you so much.
-
Would u have any records with time part in uMonth column:
'9/1/2006 00:15:00' or '9/1/2006 23:55:59'
If you have time part then your between won't get u all the data as neither '9/1/2006 00:15:00' or '9/1/2006 23:55:59' is between '9/1/2006 00:00:00' and '9/1/2006 00:00:00'
Thanks
Sreejith
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply