How to print GETDATE() in SQL Server with milliseconds in time?

Question

I want to print GETDATE() in SQL Server 2008, I need the time with milliseconds (this is for debugging purpose - to find sp's execution time )

I find this Difference

• SELECT GETDATE() returns 2011-03-15 18:43:44.100
• print GETDATE() returns Mar 15 2011 6:44PM

I think SQL Server automatically typecast in print functionality.

I need to print the date like this 2011-03-15 18:43:44.100

Thanks for your help.

Gass

Answer 1

First, you should probably use SYSDATETIME() if you're looking for more precision.

To format your data with milliseconds, try CONVERT(varchar, SYSDATETIME(), 121).

For other formats, check out the MSDN page on CAST and CONVERT.

Eyde

Print CAST(GETDATE() as Datetime2(7)) This is too works, Thanks for your answer. – Gass

PRINT CAST(SYSDATETIME() AS NVARCHAR(200)) works like a charm – Wichern

Answer 2

SELECT CONVERT( VARCHAR(24), GETDATE(), 113)

UPDATE

PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))

Scrawny

Answer 3

If your SQL Server version supports the function FORMAT you could do it like this:

select format(getdate(), 'yyyy-MM-dd HH:mm:ss.fff')

Bagley

Answer 4

these 2 are the same:

Print CAST(GETDATE() as Datetime2 (3) )
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))

UPDATE:

better than datetime is datetime2(3)

declare @dt datetime2(3)=sysdatetime()
print @dt
Print CAST(sysdatetime() as Datetime2(3) )
print sysdatetime()

Colter

Answer 5

This is equivalent to new Date().getTime() in JavaScript :

Use the below statement to get the time in seconds.

SELECT  cast(DATEDIFF(s, '1970-01-01 00:00:00.000', '2016-12-09 16:22:17.897' ) as bigint)

Use the below statement to get the time in milliseconds.

SELECT  cast(DATEDIFF(s, '1970-01-01 00:00:00.000', '2016-12-09 16:22:17.897' ) as bigint)  * 1000

Steno

Answer 6

Try Following

DECLARE @formatted_datetime char(23)
SET @formatted_datetime = CONVERT(char(23), GETDATE(), 121)
print @formatted_datetime

Blindage

Answer 7

Create a function with return format yyyy-mm-hh hh:mi:ss.sss

create function fn_retornaFecha (@i_fecha datetime)
returns varchar(23)
as
begin
declare 
@w_fecha varchar(23),
@w_anio  varchar(4),
@w_mes   varchar(2),
@w_dia   varchar(2),
@w_hh    varchar(2),
@w_nn    varchar(2),
@w_ss    varchar(2),
@w_sss   varchar(3)

select @w_fecha = null

if ltrim(rtrim(@i_fecha)) is not null 
begin
   select
   @w_anio = replicate('0',4-char_length( convert(varchar(4), year(@i_fecha)) )) + convert(varchar(4), year(@i_fecha)),
   @w_mes  = replicate('0',2-char_length( convert(varchar(2),month(@i_fecha)) )) + convert(varchar(2),month(@i_fecha)),
   @w_dia  = replicate('0',2-char_length( convert(varchar(2),  day(@i_fecha)) )) + convert(varchar(2),  day(@i_fecha))  ,
   @w_hh   = replicate('0',2-char_length( convert(varchar(2),datepart( hh, @i_fecha ) ) )) + convert(varchar(2),datepart( hh, @i_fecha ) ),
   @w_nn   = replicate('0',2-char_length( convert(varchar(2),datepart( mi, @i_fecha ) ) )) + convert(varchar(2),datepart( mi, @i_fecha ) ),
   @w_ss   = replicate('0',2-char_length( convert(varchar(2),datepart( ss, @i_fecha ) ) )) + convert(varchar(2),datepart( ss, @i_fecha ) ),
   @w_sss  = convert(varchar(3),datepart( ms, @i_fecha ) ) + replicate('0',3-DATALENGTH( convert(varchar(3),datepart( ms, @i_fecha ) )  ))


   select @w_fecha = @w_anio + '-' + @w_mes + '-' + @w_dia + ' ' + @w_hh + ':' + @w_nn + ':' + @w_ss + '.' + @w_sss
end

return @w_fecha

end
go

Example

select fn_retornaFecha(getdate())

and the result is: 2016-12-21 10:12:50.123

Armillary