CODEWITHSUNDEEP
sql-servert-sqlgetdate
Serg
Serg

Reputation: 93

Getting the current date in SQL Server

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]

Upvotes: 3

Views: 9573

Answers (2)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

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.

Upvotes: 3

Tschallacka
Tschallacka

Reputation: 28712

DECLARE @date datetime set @date = GETDATE()

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

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

enter image description here

Upvotes: 6

Related Questions