52

Want to get current datetime to insert into lastModifiedTime column. I am using MySQL database. My questions are:

  1. is there a function available in SQL? or

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

  3. what is the function available in MySQL?

DineshDB
  • 5,998
  • 7
  • 33
  • 49
5YrsLaterDBA
  • 33,370
  • 43
  • 136
  • 210
  • 8
    Wouldn't it be nice if ANSI-SQL required that these basic system functions were named consistently across platforms? Standardize (substring or substr) or (IsNull or NVL). – Bill Aug 05 '09 at 14:35

9 Answers9

80

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
Eduardo Molteni
  • 38,786
  • 23
  • 141
  • 206
11

I always just use NOW():

INSERT INTO table (lastModifiedTime) VALUES (NOW())

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_now

Nathan Loding
  • 3,185
  • 2
  • 37
  • 43
11

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

CURDATE() returns 2009-08-05

CURTIME() returns 15:13:00

Sachith Wickramaarachchi
  • 5,546
  • 6
  • 39
  • 68
Joseph Mansfield
  • 108,238
  • 20
  • 242
  • 324
9
SYSDATETIME() 2007-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME() 2007-04-30 20:10:02.0474381
CURRENT_TIMESTAMP 2007-04-30 13:10:02.047 +
GETDATE() 2007-04-30 13:10:02.047 
GETUTCDATE() 2007-04-30 20:10:02.047

I guess NOW() doesn't work sometime and gives error 'NOW' is not a recognized built-in function name.

Hope it helps!!! Thank You. https://learn.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql

Naive
  • 345
  • 2
  • 6
  • 18
8

I want my datetime, and I want it now()!

For MySQL, anyway.

Matthew Jones
  • 25,644
  • 17
  • 102
  • 155
3
  1. GETDATE() or GETUTCDATE() are now superseded by the richer SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET (in SQL 2008)
  2. Yes, I don't think ANSI has ever declared anything, and so each manufacturer has their own.
  3. That would be NOW()

Hope this helps...

Rob

Sachith Wickramaarachchi
  • 5,546
  • 6
  • 39
  • 68
Rob Farley
  • 15,625
  • 5
  • 44
  • 58
2

For SQL Server use GetDate() or current_timestamp. You can format the result with the Convert(dataType,value,format). Tag your question with the correct Database Server.

Saif Khan
  • 18,402
  • 29
  • 102
  • 147
2

Just an add on for SQLite you can also use 

CURRENT_TIME
CURRENT_DATE
CURRENT_TIMESTAMP

for the current time, current date and current timestamp. You will get the same results as for SQL

pygalaxy
  • 23
  • 7
1

try this

SELECT CURTIME(); return 23:12:58

SELECT CURDATE(); return 2020-11-12

SELECT NOW(); return 2020-11-12 23:19:26

SELECT DAY(now()); return 12

SELECT DAYNAME(now()); return Thursday

Hope this would be helpful for you.