How to Get Current Datetime in SQL

how to get current datetime in SQL?

Complete answer:

1. Is there a function available in SQL?

Yes, the SQL 92 spec, Oct 97, pg. 171, section 6.16 specifies this functions:

CURRENT_TIME       Time of day at moment of evaluation
CURRENT_DATE       Date at moment of evaluation
CURRENT_TIMESTAMP  Date & Time at moment of evaluation

2. It is implementation depended so each database has its own function for this?

Each database has its own implementations, but they have to implement the three function above if they comply with the SQL 92 specification (but depends on the version of the spec)

3. What is the function available in MySQL?

NOW() returns 2009-08-05 15:13:00  
CURDATE() returns 2009-08-05  
CURTIME() returns 15:13:00  

Getting the current date in SQL Server

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.

SQL GetDate() displays current date or system date?

It is clearly said in MSDN:

getdate() returns the current database system timestamp as a datetime value
without the database time zone offset. This value is derived from the
operating system of the computer on which the instance of SQL Server
is running.

SQL Server: How to get current date time in YYYYMMDDHHMISSMSS

select replace(
       replace(
       replace(
       replace(convert(varchar(23), getdate(), 121),
       '-',''),
       '.',''),
       ' ',''),
       ':','')

How to get current date & time in MySQL?

You can use NOW():

INSERT INTO servers (server_name, online_status, exchange, disk_space, network_shares, c_time)
VALUES('m1', 'ONLINE', 'exchange', 'disk_space', 'network_shares', NOW())

How to get Time from DateTime format in SQL?

SQL Server 2008:

SELECT cast(AttDate as time) [time]
FROM yourtable

Earlier versions:

SELECT convert(char(5), AttDate, 108) [time]
FROM yourtable

Adding current date time in SQL Table using SSMS Edit Mode

You can't manually call GETDATE() in edit mode. Instead, add GETDATE() as the default value for the column in design mode, and refrain from adding a value to the column in edit mode. Then SQL Server will add the current date-time as the value.

---

Related Topics