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

User · Answer

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

User · Answer

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

User · Answer

SELECT CONVERT  VARCHAR 24   GETDATE    113    UPDATE  PRINT  CONVERT  VARCHAR 24   GETDATE    121

User · Answer

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

User · Answer

these 2 are the same   Print CAST GETDATE   as Datetime2  3    PRINT  CONVERT  VARCHAR 24   GETDATE    121

User · Answer

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

User · Answer

Try Following  DECLARE  formatted datetime char 23  SET  formatted datetime   CONVERT char 23   GETDATE    121  print  formatted datetime

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to datetime

Examples related to getdate