Sunday, February 19, 2012

How format the time

I am looking for a way to retrieve the time from a Sql 2000 database in the format of hh:mm AM or PM. I am storing the information as a "datetime" data type. If I only supply the time (such as 02:30 PM) then the date defaults to 12/30/1899. When I read the information from the database I get "1899-12-30 14:30:00:000".

If I enter 02:30 PM I would like to get 02:30 PM.

I have tried various convert functions in Sql, maybe there is a way to format with an ASP.Net datetime function but I need help to get the information back in the right format.
Thx In AdvanceYou would need to format as an nvarchar:

SELECT RIGHT(CONVERT(nvarchar(20),GetDate(),100),7)

This will be close (no space between minutes and AM or PM).

You can format easily in .NET

DateTime.Now.ToString("hh:mm tt")|||That worked!!!
I would also like to know what is going on in this peice of code, at your convenience.

Thank you so much|||In this code:

SELECT RIGHT(CONVERT(nvarchar(20),GetDate(),100),7)

I am CONVERTing the current datetime to an nvarchar() using format 100, which ends with the last 7 characters hh:mmtt

In .NET, the ToString() method is overloaded, and hh gets the hours in 12 hour format, mm gets the minutes, and tt gets the AM or PM.

DateTime.Now.ToString("hh:mm tt")

No comments:

Post a Comment