Converting datetime (time portion) to varchar

20 pts.
Tags:
Database
IBM DB2
Oracle
SQL Server
I have an issue with a report I am converting from Crystal Reports to SRS. The reports datasource is a SQL stored procedure, that converts a datetime column to a varchar column containing just the time portion of the data. The time is converted to 24 hr format. CR was able to with it's built in syntax to convert this to a 12 hr format with AM & PM. I have not found a way to do this within SRS and have been struggling to determine if there is something I can change in the stored procedure to convert the time to 12 hr format, instead of the 24 hr format. I have tried the CAST and CONVERT, with no luck. I know that they must be a way to handle this situtation, relativly easy, but I have a limited amount of Transact-SQL expertise, and turn to some expert advise. Here is the original statement from the SP: isnull(CONVERT(VARCHAR(5),@IND,114),'') @IND datatype is datetime Your help is appreciated greatly. L.Cain

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you are using MSSQL then you can use the convert function like this:

select convert(char(11),getdate(),108)

the 108 at the end is a canned format for just the time in 24 hour format.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • MooseDrool
    Sorry, I see you already had that in your query. Is the original query also assigned a name for the column? like this: select isnull(convert(varchar(5),getdate(),108),'')as 'MyTime' Some report engines require a column name for all columns.
    0 pointsBadges:
    report
  • JSATSOW
    I would probably use the code below. Once the conversion to VarChar is complete, standard string manipulation works. The conversion to format '0' is a default format of 'mon dd yyyy hh:miAM (or PM)'. The RIGHT statement strips the time out of the string. The REPLACE changes the leading space to a 0. I do not know what SRS is. Replace(Right(Convert(VarChar(19),@IND,0),7),' ','0')
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following