当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - format current time to yyyy-MM-dd 00:00:00

问题描述:

By using the below query i get the first day of month of current date

 SELECT DATEADD(dd,-(DAY(getdate())-1),getdate())

result: 2014-04-01 15:41:06.257

I need change hour, minus, second in 00:00:00 format, I Want something like

2014-04-01 00:00:00

Thanks

网友答案:

Your query returns a datetime value — the count of milliseconds since the SQL Server clock's epoch of 1 Jan 1900 00:00:00.000.

A datetime value has only the textual representation you give it (and if you don't explicity specify it, you'll get the default configured for your SQL Server).

You can do any of the following:

  • Convert it to a date value (assuming a recent-ish version of SQL Server):

    select convert(date,
             dateadd(day,
               1-day(current_timestamp) ,
               current_timestamp
               )
             )
    
  • You can strip the time component by doing a little date/time arithmetic:

    select dateadd(day,
             datediff(day,
               '', -- 1 Jan 1900 00:00:00.000
               current_timestamp
               ) - day(current_timestamp) + 1,
               '', -- 1 Jan 1900 00:00:00.000
             )
    
  • Since a datetime value is a pair of 32-bit signed integers, you can manipulate its internal representation to chuck the time component:

    select convert(datetime,
             substring(
               convert(varbinary(8),current_timestamp),
               1,
               4
               ) + 0x00000000
             )
    

If what you actually want is a textual representation of the date for display, you can simply convert to to the desired form:

select convert(char(10),
         dateadd(day,
           1-day(current_timestamp) ,
           current_timestamp
           ),
         120
         )
网友答案:

This will get there, too:

select dateadd(month, datediff(month, 0, getdate()), 0)

分享给朋友:
您可能感兴趣的文章:
随机阅读: