123

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.

Marcello Miorelli
  • 3,368
  • 4
  • 44
  • 67
Ramakrishnan
  • 5,254
  • 9
  • 34
  • 41

7 Answers7

132

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.

Adam Robinson
  • 182,639
  • 35
  • 285
  • 343
61
SELECT CONVERT( VARCHAR(24), GETDATE(), 113)

UPDATE

PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))
Kris Ivanov
  • 10,476
  • 1
  • 24
  • 35
51

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')
Stefan Michev
  • 4,795
  • 3
  • 35
  • 30
17

these 2 are the same:

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

enter image description here

UPDATE:

better than datetime is datetime2(3)

enter image description here

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

enter image description here

Marcello Miorelli
  • 3,368
  • 4
  • 44
  • 67
3

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
Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
S Kotra
  • 81
  • 2
1

Try Following

DECLARE @formatted_datetime char(23)
SET @formatted_datetime = CONVERT(char(23), GETDATE(), 121)
print @formatted_datetime
Lav
  • 1,850
  • 15
  • 17
0

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

admincedep
  • 9
  • 1