Getting the current date in SQL Server

Question

I searched but wasn't able to find the way to get the date in this format (DD.MM.YYYY) Help me please change this request:

DECLARE @date datetime
set @date = '01.05.2016'
SELECT [User], cast(DATEADD(SECOND, sum(datediff(DAY, @date,[Start])),@date) as date)'Date'
      ,cast(DATEADD(SECOND, sum(datediff(SECOND, '00:00:00',[Period])),'00:00:00') as time)'Total time'
  FROM [Table].[TableAction]
  where
   [Start] >= @date+'00:00:00' and [Start] <= @date+'23:59:59'
   group by [USER]

what did you try, did you google the problem, it is common sense. we are not here to do your homework — swe, Sep 08 '16 at 06:32

score 6 · Answer 1 · answered Sep 08 '16 at 06:29

6

DECLARE @date datetime set @date = GETDATE()

Now to output it, you need to "Format" it.

select FORMAT (@date,'MM.dd.yy') as date

answered Sep 08 '16 at 06:29

Tschallacka

27,901
14
88
133

score 3 · Accepted Answer · edited May 23 '17 at 12:14

The best practice is to store the datetime in datetime format in the database and whenever you need the data you can access it and format it according to your need.

 DECLARE @Currentdate DATETIME;
 SET @Currentdate=GETDATE();   -- Store cuurent date into variable

And then when you want to display it use the below to format it as dd.MM.YYYY

SELECT CONVERT(VARCHAR(10),GETDATE(),104);  -- format the @Currentdate to the required format.

FORMAT works only in SQL Server 2012+. If your database is SQL server 2008 or 2005 FORMAT doesn't work.In that case, you can go for the CONVERT function.

So, If your database is above SQL SERVER 2012, you can go for FORMAT as suggested by Tschallacka

DECLARE @Currentdate DATETIME=GETDATE(); -- Store cuurent date into variable

And then when you want to display it use the below to format it as dd.MM.YYYY

SELECT FORMAT(@Currentdate,'dd.MM.yyyy')  -- format the @Currentdate to the required format.

Getting the current date in SQL Server

2 Answers2

Linked