April 1, 2015 at 9:47 am
A strange one this. I have two data parameters, both of which have their default values set from a dataset.
I've noticed in the datetime picker that if the time is 00:00:00 only the date is shown (dd/mm/yyyy) however if the time is not 00:00:00 then the format changes to dd/mm/yyyy hh:mm
Why is it doing this and secondly what can I do to force it to always be dd/mm/yyyy hh:mm
Just to reinforce, this is the DateTime picker that needs formatting not formatting in the report.
SQL Server 2012
Thanks for any assistance.
Giles
April 1, 2015 at 9:18 pm
Why is it doing this
It has to do with how SSRS explicitly converts values. If the time is 00:00:00 it is assuming you want to treat the values as a date; otherwise it wants to treat the value as a datetime.
what can I do to force it to always be dd/mm/yyyy hh:mm
Inside the query that feeds your dataset you can convert the date value to a varchar, then replace the text '00:00:00' with '00:00:01' then convert it back to datetime. Note my example below:
DECLARE @dt datetime = '1/1/2012 00:00:00';
SELECT
date_original = @dt,
date_new = CONVERT(datetime, REPLACE(CONVERT(varchar(30), @dt, 20),'00:00:00','00:00:01'))
Results:
date_original date_new
----------------------- -----------------------
2012-01-01 00:00:00.000 2012-01-01 00:00:01.000
-- Itzik Ben-Gan 2001
April 2, 2015 at 1:30 am
Thanks Alan, I really appreciate you taking the time to answer my post.
Unfortunately passing that additional second through to the underlying stored proc will miss vital data, so that's not going to be a solution for me.
April 2, 2015 at 9:24 am
Understood. That's all I could think of, hopefully someone else chimes in.
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply