So its been a while since I have been looking for a way to do this. On the DOS command prompt, I need the date formatted as say dd-mm-yyyy or yyyy-mm-dd. How do you accomplish that? Its been a mystery for me for quite a while, till I read this excellent article and said “Thank You” out loud thrice over.

So here’s the logic behind this:
%date:~-2,2%\%date:~0,2%\%date:~3,2%

DOS already stores the %DATE% system variable. So if you do a echo %date% DOS should give you the current date.

Depending upon the format of this output, you can customize the format of the date by using substrings. Lets take a closer look at the command string above. If you notice %date is appended by a colon(:) and a tilde(~). This is the syntax to extract a substring from the specified parameter (in this case the %date% output).

So when we do a echo %date:~3,2%, DOS returns us the substring from the output of %DATE% from character 4(3+1, remember DOS string indexes start from 0), extending for 2 characters, including the 4th character. You can use the negative symbol(-) to start from the end of the output of the date string, as %date:~-2,2 shows. It extracts 2 characters from the end of the output of %DATE%.

You can obviously try this technique out on different command texts like %TIME%, %DIR% and %SYSDIR% etc. to do a lot more than you could ever do.

In case you are wondering how could this possibly be helpful to anyone, ask System Administrators, Database Administrators and Data Warehousing Specialists, who frequently need to rename files based on the current date and time.

Note: I have tested this on 2 machines running Win XP Pro and 1 machine on Win 2000. It works fine on both. Other platforms still require testing

An excellent read on the different ways DBMSs handle sequences and identity columns. While only IBM DB2 supports both, Oracle and SQL Server have their own nooks and crannies which need to be explored in depth. This article does an excellent job at it.

http://www.dbazine.com/db2/db2-disarticles/gulutzan4

If you want to convert an IDENTITY column which is generated by DEFAULT to be generated ALWAYS, you are out of luck. You cant do it using an ALTER statement. The only logical way is to drop and recreate the table as stated here.

How do you retrieve the value of an IDENTITY just inserted into a row? Use the function called IDENTITY_VAL_LOCAL as shown here.