In my database, I have to perform CRUD operations based on the last date modified field.

DateLastModified

2021-09-09 16:46:05.670
....

In my SP, I am getting a date using getdate() function but it shows me the date something like this

set  @TodayDate = getdate()

May 17 2022  3:04PM

How can I get the date in the format shown below?

2022-05-17 15:04:00.113

CodePudding user response：

You are looking for this:

select convert(varchar(23), getdate(), 21)

see the reference: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/#:~:text=How to get different date formats in SQL,use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 23)

CodePudding user response：

Use SELECT instead of PRINT inside your procedure.

PRINT implicitly converts a date/time to a string using a specific style (0 IIRC). The full list of style numbers are documented here.

SELECT does no such shenanigan - it sends the original data type to the client, though the client is still free to render that in any way it pleases. It just happens that SSMS will render a datetime value the way you want to see it (which happens to be style 21 / 121).

• Example db<>fiddle