CAML Query Where Criteria for Date type columns and date formats

When I started writing a CAML query which involved checking against a certain date in a date time type column in one of my list, I thought that it would be as simple as

string CAMLQuery ="<Where>"+ "<And>"+ "<Leq>FieldRef Name='Start_x0020_Date'/>"+ "<Value Type='DateTime'>" + DateTime.Now + "</Value></Leq>"+ "<Geq><FieldRef Name='End_x0020_Date'/>"+ "<Value Type='DateTime'>" + DateTime.Now + "</Value></Geq>"+ "</And>" + "</Where>";

assuming that it will return me results where Start Date is less than today and End Date is greater than today meaning that everything that needs to be shown today will come up but as it turned out it was not the case.
CAML Queries expects a certain datetime format when it comes to use them in WHERE Clauses.
You can use a complex

SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now)

or you can use

String.Format("{0:s}", DateTime.Now)

which results in

string CAMLQuery ="<Where>"+ "<And>"+ "<Leq>FieldRef Name='Start_x0020_Date'/>"+ "<Value Type='DateTime'>" + String.Format("{0:s}", DateTime.Now) + "</Value></Leq>"+ "<Geq><FieldRef Name='End_x0020_Date'/>"+ "<Value Type='DateTime'>" + String.Format("{0:s}", DateTime.Now) + "</Value></Geq>"+ "</And>" + "</Where>";

One of the solutions I found on internet was to use an Offset, something like

string CAMLQuery = "<Where> <Geq> <FieldRef Name='MyDateColumn' /> <Value Type='DateTime> <Today OffsetDays='-5' /> </Value> </Geq> </Where>";

which means 5 days less than today.
You might also be interested in IncludeTimeValue attribute which can a TRUE or a FALSE value