SQL Server Performance

Date -Time conversion

Discussion in 'SQL Server 2005 Reporting Services' started by satheesh.s, Feb 12, 2007.

  1. satheesh.s New Member

    25-2-2005 8:00 AM.This is the data in the database.I Want to produce the report like 8:00 A.How can i change and produce this results
  2. Adriaan New Member

    For the proper solution, look at CONVERT in books online. At least in SQL 2000, you can use CONVERT(VARCHAR, &lt;date_col&gt;, 14) to get the time only - but only on a 24hr clock - no AM or PM.<br /><br />For a quick and dirty solution, why not use the SUBSTRING and LEN function to get positions (end-7) to (end-1), with LTRIM to suppress the space in case the hour is before 10:<br /><br />SELECT LTRIM(SUBSTRING(&lt;date_column&gt;, LEN(&lt;date_column&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /> - 7, LEN(&lt;date_column&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /> - <img src='/community/emoticons/emotion-11.gif' alt='8)' />)
  3. ndinakar Member

    You can either do it using SQL functions in your query/proc or use VB.NET function in the Expression window -whichever works best for you.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  4. satya Moderator

    http://www.sql-server-performance.com/fk_datetime.asp for more information.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  5. Madhivanan Moderator

    Doesnt MSRS support DATE formats?

    Madhivanan

    Failing to plan is Planning to fail
  6. satya Moderator

    Yes it does and all the default settings are originated from SQL SErver.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  7. Madhivanan Moderator

    Then I would suggest using Formating there [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  8. ranjitjain New Member

    If SQL DateTime Functions are available in MSRS, then
    another solution could be to use DATEPART(hh,Datecolumn)
  9. ndinakar Member

    Satya

    AFATIK, you cannot use SQL functions in Expressions (at least in VS 2003). It only takes VB.NET code. That is why I said either use SQL functions within the SQL query and get the output already formatted or do the formatting using VB.NET functions.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************

Share This Page