Hi,
This is mostly a SQL question and I'm having issues figuring it out. It has to do with converting the datetime format in Post dates for GL accounts.
We have a custom procedure/report that is slightly different from the GL_SUMMARY_REPORT. Basically, the question I have is that our finance team wants the date format for this report to be 'mm/dd/yyyy' and though I think i'm doing it right, it doesn't seem to be working!
Apparently, before we upgraded to V12, the report output when saved in excel did save in the date format that they wanted, but I'm not sure why that would have anything to do with it. The main issues is, now that we are in V12, this format is different and will not import into our excel macro, which is then uploaded into our accounting software and how our finance department does their job.
I have suggested altering the macro to be compatible to the datetime format in Tessitura, which at this point is 'YYYY-MM-DD HH:MM:SS:MMM", but they insist that I change it back to the way it was before the upgrade. *sigh*
I have found the procedure this report uses to run and have found the code I think I would need to alter (just the output, right?) , but I'm not sure how to CONVERT the post_dt which is also in datetime. I've been trying CONVERT to various different format types, but I think my syntax might be wrong. In my testing, all the results after I insert the CONVERT function, are coming back with incorrect dates ("1900-01-01 .........")
Any help would be great. I must be missing something obvious!
Here's the sproc with notes on where I'm having the trouble.
Normal 0 false false false EN-US X-NONE X-NONE
select a.gl_act_no ,
a.type ,
a.post_amt ,
b.gl_description,
a.post_no,
a.post_dt, ---This is the date I want!
debit = convert(money,0),
credit = convert(money,0),
net = convert(money,0),
mode = convert(int,0),
fyear
into #temp
FROM T_GL_POSTING_HISTORY a
LEFT OUTER JOIN T_GL_ACCOUNT b ON a.gl_act_no = b.gl_account_no
where 2=1
insert into #temp
SELECT a.gl_act_no ,
gl_description = ISNULL(b.gl_description, '***UNKNOWN***'),
a.post_dt,--Again, here's the datetime
debit = case when a.type = 'D' then a.post_amt else 0 end,
credit = case when a.type = 'C' then a.post_amt else 0 end,
mode = @mode,
a.fyear
WHERE a.post_dt >= @post_dt_start and
a.post_dt <= @post_dt_end and
exists (select * from t_batch where post_no = a.post_no) and --
a.post_no in (select post_no from t_batch where batch_type in
(select id from tr_batch_type where description like 'T:%'))
SELECT gl_act_no ,
gl_description = max(gl_description),
post_no,
post_dt,---= CONVERT(datetime,110),---This is the output, so is this where I would change the date format??
debit = sum(debit),
credit = sum(credit),
net = sum(debit) - sum(credit),
mode = max(mode),
FROM #temp
group by gl_act_no, fyear, post_no,post_dt
GO
Hi Tiffany,
Would something like this give you what you need?
select convert(varchar,datepart(mm,post_dt))+
'/'+convert(varchar,datepart(dd,post_dt))+
'/'+convert(varchar,datepart(yyyy,post_dt)) as posting_dt
Oh yes! That did the trick, Jonathan!
Thank you all! You've all been very helpful!