none
Can I Format Date in a Select statement for a dataset

    Question

  • I have a query the populates a dataset then passing it to a parameter that works.  I want to clean up the user side and I am trying to format a date.  I don't know if this is possible, but here is the query that populates the dataset.

    Select Distinct Format(Period, "MM YYYY") as PeriodID, Period
    From vwCACombinedTech
    Order BY Period

    Is there a solution?

    Thanks

     

    Friday, March 12, 2010 9:55 PM

Answers

  • Try this as your SQL statement:

    Select Distinct CONVERT(VARCHAR(8),Period,1) as PeriodID, Period
    From vwCACombinedTech
    Order BY Period


    The dates will now be in MM/DD/YY format.  If you want them in MM/DD/YYYY format, do (VARCHAR(10),Period,101) instead.



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.

    • Marked as answer by DaveKStLMo Friday, March 12, 2010 11:01 PM
    Friday, March 12, 2010 10:51 PM

All replies

  • It's a little unclear exactly what your problem is.  What is the current output?  What is your desired output?  Is the current date formatting not working?

    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    Friday, March 12, 2010 10:15 PM
  • you can always get the date and then format the textbox in whcih you are showing it in the report.
    Friday, March 12, 2010 10:17 PM
  • The SQL statement does not work because of the Format.  Without the format it returns 12/31/2009 00:00:00 and I am hoping to either have it display Dec 2009 or 12/31/2009 in the dropdown.  This is populating the parameter field.

    Dave
    Friday, March 12, 2010 10:42 PM
  • Try this as your SQL statement:

    Select Distinct CONVERT(VARCHAR(8),Period,1) as PeriodID, Period
    From vwCACombinedTech
    Order BY Period


    The dates will now be in MM/DD/YY format.  If you want them in MM/DD/YYYY format, do (VARCHAR(10),Period,101) instead.



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.

    • Marked as answer by DaveKStLMo Friday, March 12, 2010 11:01 PM
    Friday, March 12, 2010 10:51 PM
  • you can do this

    select

     

    convert(char(10), getdate(), 101)

    • Proposed as answer by Parry2k Friday, March 12, 2010 11:04 PM
    Friday, March 12, 2010 10:53 PM
  • Thank you both We have a winner!!!!

    Dave :-)
    Friday, March 12, 2010 11:02 PM