The sole difference is that a console application always spawns a console if it isn’t started from one (or the console is actively suppressed on startup). A windows application, on the other hand, does not spawn a console. It can still attach to an existant console or create a new one using AllocConsole.
This makes Windows applications better suited for GUI applications or background applications because you usually don’t want to have a terminal window created for those.
On a more technical note, the only difference between a Console and a Windows executable is one byte in the PE header of the exe file. Toggling this byte manually (e.g. using a hex editor) converts the application type. This is a well-published hack that is used to create console applications in VB6 (where this type of application was not explicitly supported).
To determine and change the subsystem type of an application, you need to read parts of the PE header. The address of the subsystem data is not fixed though, because it’s part of the optional file header whose position is determined by an address stored in the DOS file header (in the member e_lfanew). This address actually points to the _IMAGE_NT_HEADERS record which, in turn, includes the IMAGE_OPTIONAL_HEADER32 structure. This has an int161) member called Subsystem. The member’s value is 2 for a Windows application and 3 for a console application. Other subsystems exist (in particular, POSIX and kernel).
I’ve written a small VB6 application to change the subsystem of an application, which can be downloaded from ActiveVB as source code.
The PE format isn’t very well documented but this document may serve as an introduction: Peering Inside the PE: A Tour of the Win32 Portable Executable File Format.
1) This doesn’t really contradict my claim that only one byte differs: the most significant byte of this member is always 0. Only the least significant byte changes.