Formatting Date value in vb.net still returning the wrong value

Question

The code below is the code I'm using to set the selection formula for my Crystal Report.

Dim dateTo As Date
 dateTo = dtpCRTo.Value.ToShortDateString
 dateTo = Format(dateTo, "dd/MM/yyyy")

If cmbCRSupplier.Value = "" Then
   selectionFormula = "{Sales_Headers.Stage} = '6' AND {Sales_Lines.PaymentDate} <= #" & dateTo & "#"

The issue I'm getting, is the data that I'm testing with has a Sales_Headers.Stage value of 6, which is fine, and a Sales_Lines.PaymentDate value of 30/11/2016 (In UK, dd/MM/yyyy) format, however, even though the DateTimePicker I'm using is showing 11th December 2016, the value of dateTo is returning as 12/11/2016, which is MM/dd/yyyy format, so the report shows no data, as that date is before the payment date.

Why is the date returning in this format, even though I'm formatting it?

EDIT

Also, before I put the formatting section in, and had dtpCRTo.Value.ToShortDateString in the selection formula, it still did the same thing.

For any dates where the day part is less than 13, it treats that part as a month, but works fine once the day part is 13 or more...

EDIT 2:

The query I have now is as follows:

 Dim dateTo As Date = dtpCRTo.Value.AddDays(1).Date
   dateTo = Format(dateTo, "dd/MM/yyyy")
   If cmbCRSupplier.Value = "" Then
    selectionFormula = "{Sales_Headers.Stage} = '6' AND {Sales_Lines.PaymentDate} < #" & dateTo & "#"

This works for all dates, except when choosing the last day of each month. If I'm selecting 31st January, the dateTo value will become 02/01/2017 (2nd February), which is treated in the formula as 2nd January. Is there a way around this?

unless PaymentDate is a text column, the format doesnt matter because dates do not have a format. — Ňɏssa Pøngjǣrdenlarp
@Plutonix No, PaymentDate is Date/Time format, but even before adding in the formatting bit it was still not showing the data — Harambe
a DateTime type will always have a date and a time portion, so using <= you dont want the time elements to exclude it. Try this: dateTo = dtpCRTo.Value.AddDays(1).Date that should allow all dates with any time for that date to be included. — Ňɏssa Pøngjǣrdenlarp
Could be - thats why the first thing I asked was how the date is saved. If the column is date then pass date. See How to compare two dates FORMATS for saving to DB — Ňɏssa Pøngjǣrdenlarp
get rid of Format(...) Dates do not have a format, if you want dates to act like dates, use dates. Its been a while since I used CR but typically you would define a date parameter dateTo in it, and the formula would them be ...AND {Sales_Lines.PaymentDate} < {?dateTo} "formatting" the date to your culture is the problem because if anything, CR probably expects InvariantCulture. Option Strict is still off. — Ňɏssa Pøngjǣrdenlarp

Vijunav Vastivch Vijunav Vastivch · Accepted Answer · 2017-01-27T01:54:56

Format Properties:

1. DatePicker > Properties > CustomFormat > (Enter format you want here)
2. Properties > Format > Custom

Code Behind:

Dim dateFormat As String
dt.Format = DateTimePickerFormat.Custom
dt.CustomFormat = "dd/MM/yyyy"
dateFormat = dt.Text

Its either you want to format in Properties or in Code Behind.

Formatting Date value in vb.net still returning the wrong value

1 Answers