system
November 12, 2012, 6:39pm
#1
Hello, what is the syntax to convert a year object (YYYY) from Numeric to character? Am using SQL.
TIMEDIM_SO_BILLING_DATE.FISCAL_YEAR
Secondly, can anyone recommend sites to lookup SQL functions to define Universe objects? Somewhere simple without alot of verbage - Syntax, explanation and example. There’s a couple of really good sites, that don’t mince words. Microsoft’s site thus far, has been unhelpful.
Thank you,
Report Writer (BOB member since 2002-09-25)
system
November 12, 2012, 7:14pm
#2
Hi,
Which database do you use?
And, this can help:
As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server.
Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8.
Yesterday
cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime)
Today
cast(convert(char(10),getdate(),23) as datetime)
The following also works for today:
dateadd(dd, datediff(dd,0,getdate()), 0)
Start of Current Month
cast(convert(char(7),getdate(),23)+'-01' as datetime)
End of Current Month
dateadd(d,-1…
Marek Chladny (BOB member since 2003-11-27)
system
November 12, 2012, 9:05pm
#3
Hi,
SAP Sales and Distribution Module (db) of which the data is pulled into a BO Rapid Mart. The Universe connects to the Rapid Mart.
Hope this answers your question. Thanks for the link, I’ve bookmarked it.
Report Writer (BOB member since 2002-09-25)
system
November 13, 2012, 2:19am
#4
Which database - Oracle, MS SQL server, DB2, etc. - is the rapid mart hosted on?
Marek Chladny (BOB member since 2003-11-27)
MarkP
November 13, 2012, 12:02pm
#5
If it’s SQL Server, this will work:
convert(char(4),TIMEDIM_SO_BILLING_DATE.FISCAL_YEAR)
system
November 13, 2012, 2:14pm
#6
In case of Oracle, try:
to_number(TIMEDIM_SO_BILLING_DATE.FISCAL_YEAR)Marek Chladny (BOB member since 2003-11-27)
MarkP
November 13, 2012, 2:53pm
#7
Read the question Marek…
Shouldn’t it be to_char?
system
November 13, 2012, 2:58pm
#8
Yes, my bad. Did not read it properly
It should be to_char() instead of to_number() in this case.
Marek Chladny (BOB member since 2003-11-27)
(BOB member since 2002-09-25)
(BOB member since 2003-11-27)
