Formatting Date value in vb.net still returning th

问题:

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?

回答1:

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.