Hi all,

I've come accross this code and need to alter it to display a normal General Date format.

CASE WHEN p.BIRTHDTTM IS NULL THEN ''
			  ELSE RIGHT('0' + LTRIM(CONVERT(VARCHAR(20), BIRTHDTTM, 113)) , 20)
			END AS BIRTHDTTM

The original data (which is a DOB field) looks like this

1936-08-14 00:00:00.000

And the code above is turning it into this which is good but means I cannot then format in SSRS

14 Aug 1936 00:00:00

so ideally I'd like the code above to be altered so that it gives me

14/08/1936

• Edited by ProfessorFudger Thursday, July 30, 2015 10:25 AM Error in text

Try this

SELECT CONVERT( DATE,'1936-08-14 00:00:00.000',102)

Hi all,

I've come accross this code and need to alter it to display a normal General Date format.

CASE WHEN p.BIRTHDTTM IS NULL THEN ''
			  ELSE RIGHT('0' + LTRIM(CONVERT(VARCHAR(20), BIRTHDTTM, 113)) , 20)
			END AS BIRTHDTTM

The original data (which is a DOB field) looks like this

1936-08-14 00:00:00.000

And the code above is turning it into this which is good but means I cannot then format in SSRS

14 Aug 1936 00:00:00

so ideally I'd like the code above to be altered so that it gives me

14/08/1936

Good day ,

Try to use 103 instead 113 style :-)
SELECT CONVERT(VARCHAR(10), GETDATE(), 103)

I highly recommend to keep reading!

>> to display a normal General Date format

Normal for one is not Normal for the other :-)

Date and time Normal formats depends on the culture.

>>  The original data

The original date in the database actually has nothing to do with the displaying format. I highly recommend to read this blog:
Date displaying format, vs Date storing format

>> so ideally I'd like the code above to be altered so that it gives me 14/08/1936

This format fit to style 103. YOu can see more format options in this link:
https://msdn.microsoft.com/en-us/library/ms187928.aspx?f=255&MSPPError=-2147217396

Thank you, seems I was reinventing the wheel, the field was already a Date/Time field so when I took all the conversion rubbish from the code in came through into SSRS as a date/time and could be formatted accordinly.

Why you add '0'? The below works just fine

SELECT CASE WHEN '1936-08-14 00:00:00.000' IS NULL THEN ''
 ELSE CONVERT(CHAR(15),CAST('1936-08-14 00:00:00.000' AS DATE),103)
END AS BIRTHDTTM

Hi all below is list of Date/Time which is my output (raw data) in SQL Management Studio.

When I move this into SSRS and use the DateTime custom function it shows the the Date/Time correctly if there is a time element. However it's defaulting the 00:00:00.000 to 12:00:00 PM which is incorrect. Is there a fix around this at code level.

I would like the code originally used to show as Format dd/MM/yyyy hh:mm but still show the 00:00 element

DTTMOFDEATH
2015-05-15 00:00:00.000
2015-05-13 00:00:00.000
2015-05-28 09:30:00.000
2015-06-04 00:00:00.000
2015-05-19 00:00:00.000
2015-01-12 00:00:00.000
2014-12-26 00:00:00.000
2015-03-22 00:00:00.000
2015-06-19 17:40:00.000
2015-06-12 00:00:00.000
2015-06-12 13:31:00.000
2015-06-19 06:50:00.000
2015-06-03 00:00:00.000
2015-06-21 23:10:00.000
2015-04-09 00:00:00.000
2015-05-13 00:00:00.000
2015-01-29 00:00:00.000
2015-03-27 00:00:00.000
2015-05-21 00:00:00.000

sorry probably sounded to loong winded.

All I need is the code to change 2015-06-19 17:40:00.000 into 19/06/2015 17:40

The field name is called DTTMOFDEATH

Did you checked the link I posted above?!?

Check it please... you can chose any built-in format style from there :-)

Read BOL for CAST and CONVERT and pick up the appropriate style.

Hi Naomi, this the same link that I posted above from the start, and mentioned after :-)

Skip all these problems and return the column as is. Then, do your formatting in SSRS, where it's supposed to be done.

SSRS will automatically turn NULLS into blanks. This allows you to keep the datetime object, and have it treated as such in SSRS.

Hi all sorry about this I finally soved it this morning and do kind of feel stupid.

In SSRS all in needed to do was put for the format to dd/MM/yyyy HH:ss

What i did format it to dd/MM/yyy hh:ss which was why I was getting the 12:00 sorry guys, thanks for all your help on this.

• Marked as answer by ProfessorFudger 23 hours 52 minutes ago

Thanks for posting your solution for the future users :-)

+1