June 14, 2006 at 5:03 am
Hi, in the following function, the date (and time) for yesterday is worked out. I then have it pumped into a report as a string. How can I get the output in just 'DD/MM/YYYY' instead of 'DD/MM/YYY HH:MM:SS'?
Is there a way to do this?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[get_yesterday_start] (@today datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(day, -1, datediff(d,0,@today))
END
June 14, 2006 at 5:35 am
David,
you'd need to use the convert/cast function and specify what style you want the date to be in.
e.g.
RETURN convert(varchar(10),dateadd(day, -1, datediff(d,0,@today)),103)
Style 103 corresponds to dd/mm/yyyy. The different styles are listed in BOL when you look at CAST and CONVERT.
Note that you'll then be returning a string, if you need to actually return a datetime, you'd need to convert that again to a datetime.
Hope that helps,
June 14, 2006 at 7:24 am
Hmm, thanks Mark.. i'm getting somewhere (I think!). The report however won't even compile now with these changes. The new error is: "The conversion of a char type to a datetime data type resulted in an out-of-range datetime value".
Thanks in advance,
David
June 14, 2006 at 7:34 am
David,
when converting it back to datetime, you should specify the style you want as well.
In fact, if you want it returned as a datetime format you just convert it to datetime like so:
return convert(datetime, dateadd(day, -1, datediff(d,0,@today)),103)
The only problem is that the SQL Server datetime datatype contains the time so you'll get dd/mm/yyyy hh:mm:ss.mmm.
If you truly just want dd/mm/yyyy then you'll need to return char(10) instead of datetime. So your function would look like this:
ALTER FUNCTION [dbo].[get_yesterday_start] (@today datetime)
RETURNS char(10) AS
BEGIN
RETURN convert(varchar(10), dateadd(day, -1, datediff(d,0,@today)),103)
Hope that helps,
June 14, 2006 at 7:41 am
Hi Karl.
I tried that one too, and that's where I got the error from. If i leave out the convert(varchar(10)....,013) then it works fine. If I run the SP it returns the date, but with the time too unfortunately.
David
June 14, 2006 at 7:52 am
Hi David,
Just to double-check: Did you change the return datatype of the function to char(10)?
If so, it must then have to do with how your procedure is handling the returned value. What exactly are you doing with the returned value in the procedure?
If you're using the return value to evaluate against something else in your procedure then you'll need to return a datetime, and only convert to the correct char format when you're ready to output it.
June 14, 2006 at 7:58 am
Hiya.
What I'm basically trying to do is run a simple report (for yesterday's date in this case) that shows turnover.
It's querying an oracle DB (query pasted below) and the two report Parameters 'Value1' and 'Value2' are taking the output from the SP above (and another for the end of the day) and inserting into the as a string value so that when the report compiles it simply runs with yeserdays date range (instead of the user putting it in all the time) et voila.
Are you saying that i should leave the SP alone, let it do it's thing, and then do the convert just before it gets inserted as the string? If so, how is this done - I'm not a DBA, just someone fumbling my way through reporting services!
Here's the query:
SELECT
ID1.log_date,
ID1.total
FROM INCELLIGENCE.LOG_TRANSACTION_DAILY_VIEW ID1
WHERE LOG_DATE BETWEEN to_date(:Value1, 'DD/MM/YYYY') AND to_date(:Value2, 'DD/MM/YYYY') AND ID1.transaction_type='PURCHASE'
ORDER BY ID1.LOG_DATE ASC
Thanks again!
David
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply