Formatting a date field to just show the date, not minutes.

• faulknerwilliam2

  SSCrazy

  Points: 2299

  More actions

  July 6, 2015 at 3:23 pm

  #309672

  Hello

  A date field in a report returns the date value as:

  2015-07-01 13:30:27.000

  Is there any way I can script this to appear as:

  01 July 2015 (or 01-07-2015 or 01/07/2015) - basically to cut out the hours, minutes and seconds?

  The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:

  01 July 2015 13:30

  Any expertise in solving this would be very welcome. I can tidy it up in SSRS, but would love to script it instead.

  Thanks.

• Alvin Ramard

  SSC-Forever

  Points: 41190

  More actions

  July 6, 2015 at 3:38 pm

  #1810587

  faulknerwilliam2 (7/6/2015)

  ---

  Hello

  A date field in a report returns the date value as:

  2015-07-01 13:30:27.000

  Is there any way I can script this to appear as:

  01 July 2015 (or 01-07-2015 or 01/07/2015) - basically to cut out the hours, minutes and seconds?

  The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:

  01 July 2015 13:30

  Any expertise in solving this would be very welcome. I can tidy it up in SSRS, but would love to script it instead.

  Thanks.

  You could replace your date in the report with an expression which formats the data to th desired format, for example: =Format(Fields!Date.Value, "d/M/yyyy")

  This would format the date as 1/7/2015.

  ---

  
  Alvin Ramard
  Memphis PASS Chapter[/url]

  All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

  For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  July 6, 2015 at 4:17 pm

  #1810589

  faulknerwilliam2 (7/6/2015)

  ---

  Hello

  A date field in a report returns the date value as:

  2015-07-01 13:30:27.000

  Is there any way I can script this to appear as:

  01 July 2015 (or 01-07-2015 or 01/07/2015) - basically to cut out the hours, minutes and seconds?

  The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:

  01 July 2015 13:30

  Any expertise in solving this would be very welcome. I can tidy it up in SSRS, but would love to script it instead.

  Thanks.

  If you really must format it in the query, then convert(char(10),<your column>,113) will give you 01/07/2015, but it really is better to format it in SSRS, so that people from other regions can consume the data and have the date formatted in their own style.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]