Date Time stuff

I write code in VB6, VB.Net, C#, VB Script, TSQL, Java, JavaScript, and I find I’m regularly scrambling for the proper date time code for each. I find myself asking “What on earth did they name THAT date function in THIS language again? I just did this X months ago, why on earth can’t I remember…”. So I finally decided to write some of it down. Hopefully you find it useful. My intention is to update this page as I use different features.

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

In VB6
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

NOTES:
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.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: