CODEWITHSUNDEEP
sql-server
SaraDob
SaraDob

Reputation: 69

Convert Datetime in Stored Procedure

I have Stored Procedure on SQL to get the time the database last updated. When O run the Query by itself, I get Month dd yyyy hh:mm AM/PM.

But when O execute stored procedure, I get yyyy-MM-dd hh:mm:ss

I need the Month dd yyyy hh:mm AM/Pm ( Apr 1 2022 7:30AM) format. What Am I doing wrong in converting the datetime.

    ALTER PROCEDURE [dbo].[spGetDBLastUpdatedTime]
    -- Add the parameters for the stored procedure here
     @LastUpdatedTime Datetime OUTPUT  
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
        SET @LastUpdatedTime =(SELECT 
        convert(Varchar(MAX),last_user_update,100) as LastUpdatedTime
        FROM sys.dm_db_index_usage_stats
        WHERE database_id = DB_ID( 'Emp')
        AND OBJECT_ID=OBJECT_ID('tblEmployee'))
  RETURN;
END

Upvotes: 0

Views: 783

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

Real Datetime values DO NOT HAVE A STRING FORMAT AT ALL. What you see is a convenience shown you by the tool or environment where you ran the query. The actual value is a binary format. It's not really human-readable at all, but rather is more efficient for storage, transport, indexing, and operations like comparing or adding arbitrary days, months, milliseconds, etc.

If you need a specific format, still return a raw (unformatted) DateTime value from your database query, and then use the string conversion tools on whatever platform or reporting tool you're working with to get the desired format there, in the presentation level where it belongs.

Upvotes: 2

Related Questions