SQL Server Performance Forum – Threads Archive

Date -Time conversion

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

For the proper solution, look at CONVERT in books online. At least in SQL 2000, you can use CONVERT(VARCHAR, <date_col>, 14) to get the time only – but only on a 24hr clock – no AM or PM. 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: SELECT LTRIM(SUBSTRING(<date_column>, LEN(<date_column&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> – 7, LEN(<date_column&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> – <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />)

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
***********************

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.

Doesnt MSRS support DATE formats? Madhivanan Failing to plan is Planning to fail

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.

Then I would suggest using Formating there [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

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

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
***********************

]]>