In SQL for Microsoft SQL Server:
How to get the Month / Day in the format “Aug20”:
select Left(DateName(Month,getdate()),3) + Convert(varchar(50),Day(getdate()))
In SQL for Oracle
How to get all the records created in the last 15 minutes:
select * from tableName where theDate > (sysdate – (15/(24*60))
How to view the full date when selecting columns:
Option 1) Run this statement:
alter session set nls_date_format=’dd-mm-yyyy hh24:mi:ss’
Then run any sql containing a date column after.
Option 2) Us the to_char() function:
select to_char(theDateColumn, ‘DD-MON-YYYY HH:MI:SS’) as aDate from theTableName
Get the current Date / Time: Now()
Incrementing / Decrementing a date reliably, use the DateAdd() function, for decrementing just pass a negative parm. This is the best method because it will automatically handle things like leap years, months with different days (30, 31, 28, 29), etc:
dim dDateVariable as Date
dDateVariable = DateAdd(“n”, -3, Now())
More on the Interval format here: http://msdn.microsoft.com/en-us/library/cb7z8yf9
Oracle stores dates as a number, where hours, minutes and seconds are fractions of that number. So a value of 1 = 1 day, and a value of 1.125 = 1 day and .125 of a day or .125 * 24 = 3 hours. To get minutes you multiply the date by hours in a day (24) and then by minutes in a hour (60). Since 24 * 60 = 1440 many examples out there just say to multiply 1440 to get minutes.
Thanks goes out to Mike for the math correction in my original post.