Dates, Times, DateTimes, SQL, LINQ, and the Usual Confusion

Kinetic ERP
, , , , , ,
1

This comes up often enough that it is worth writing down. Note that I used AI to gather and generate much of the content below using Epicor’s “Knowlege” from help, documentation and also from the web (including EpiUsers :wink: ). I have read the results, BUT I have not tested/verified every fact. If you see any errors below, Please lets correct it together. This post can become a resource for everyone in the future.

Dates and times look simple until they are not. Sometimes you have a true date. Sometimes you have a true time. Sometimes you have a datetime. Sometimes the database field is text that only looks like a date. Sometimes the time portion matters. Sometimes it absolutely should not.

The first rule is simple…

Know what you actually have before you convert it.

A date-only value is not the same thing as a datetime at midnight. A time-only value is not the same thing as a duration. A string is not a date, even if it looks like one.

And for BAQs, there is one more important point…

Most of the time, you are not writing a whole SQL statement.

You are writing an expression inside a calculated field, a condition, or a parameterized filter. The BAQ designer builds the outer SQL. You provide the pieces.

Common SQL Server Date and Time Types

SQL Type Meaning Example
date Calendar date only 2026-06-16
time Time of day only 14:30:00
datetime Older date plus time type 2026-06-16 14:30:00.000
datetime2 Preferred modern date plus time type 2026-06-16 14:30:00.1234567
datetimeoffset Date plus time plus offset 2026-06-16 14:30:00 -06:00
varchar / nvarchar Text, not a date '06/16/2026'

For new SQL work, I generally prefer datetime2 over datetime unless there is a compatibility reason not to. If the offset matters, use datetimeoffset. If only the date matters, use date. If only the time-of-day matters, use time.

For BAQs, you usually do not get to redesign the underlying table. You deal with the field you have. So the practical skill is knowing how to shape the value correctly in calculated fields and criteria.

SQL in BAQs Is Usually an Expression

In a BAQ, do not think in terms of full SQL statements like this:

SELECT CAST(OrderHed.OrderDate AS date)
FROM Erp.OrderHed
WHERE OrderHed.OrderDate >= '2026-06-16'

That is not usually what you enter in BAQ Designer.

In a calculated field, you usually write only the expression:

CAST(OrderHed.OrderDate AS date)

In criteria, you usually define field/operator/value logic that amounts to something like this:

OrderHed.OrderDate >= @StartDate

or this:

OrderHed.OrderDate < DATEADD(day, 1, @EndDate)

That is the practical BAQ mindset.

The BAQ designer builds the query. You supply the expression.

BAQ Calculated Field Examples

DateTime to Date

Use this when the source field has a date and time, but you only want the date portion.

Calculated field expression:

CAST(OrderHed.OrderDate AS date)

or:

CONVERT(date, OrderHed.OrderDate)

Typical calculated field setup:

Property Value
Field Name OrderDateOnly
Data Type date
Expression CAST(OrderHed.OrderDate AS date)

The important point is that this returns a real date, not a formatted string.

That means it still sorts like a date, filters like a date, and behaves like a date.

DateTime to Time

Use this when you only want the time-of-day portion.

CAST(SomeTable.SomeDateTimeField AS time)

or:

CONVERT(time, SomeTable.SomeDateTimeField)

Example:

CAST(LaborDtl.ClockInDateTime_c AS time)

This is a time-of-day. It is not elapsed time. That distinction matters.

Date to Text

Use this when you need a display-friendly value.

CONVERT(varchar(10), OrderHed.OrderDate, 23)

Returns:

2026-06-16

Style 23 is ISO date format:

yyyy-mm-dd

This is fine for display. It is not ideal for further date logic because the result is now text.

DateTime to Text

For a display value with milliseconds:

CONVERT(varchar(23), SomeTable.SomeDateTimeField, 121)

Returns:

2026-06-16 14:30:45.123

For ISO-style datetime:

CONVERT(varchar(30), SomeTable.SomeDateTimeField, 126)

Returns:

2026-06-16T14:30:45.123

Useful SQL CONVERT styles:

Style Output
23 yyyy-mm-dd
108 hh:mi:ss
112 yyyymmdd
120 yyyy-mm-dd hh:mi:ss
121 yyyy-mm-dd hh:mi:ss.mmm
126 yyyy-mm-ddThh:mi:ss.mmm

There are many more style codes, but these are the ones I would normally reach for first. Prefer four-digit years. Avoid ambiguous regional formats unless you have no choice.

Time to Text

If the source is a datetime field and you want just the time as text:

CONVERT(varchar(8), SomeTable.SomeDateTimeField, 108)

Returns:

14:30:45

If the field is already a SQL time field:

CONVERT(varchar(8), SomeTable.SomeTimeField, 108)

Again, this is display logic. Once converted to text, it is no longer a time value.

Text to Date

Sometimes you inherit a UD field or imported value that contains a date stored as text.

If the text is in ISO format:

2026-06-16

You can convert it like this:

CONVERT(date, UD01.ShortChar01, 23)

The safer BAQ version is usually:

TRY_CONVERT(date, UD01.ShortChar01, 23)

If the value cannot be converted, TRY_CONVERT returns NULL instead of failing the entire query.

That is usually what you want in a BAQ. One bad row should not take down the whole query.

Text to DateTime

If the text value looks like this:

2026-06-16T14:30:45

or this:

2026-06-16T14:30:45.123

Use:

TRY_CONVERT(datetime2, UD01.Character01, 126)

The 126 style is for ISO-style datetime text.

Find Bad Text Dates

This is useful when cleaning up old UD fields where someone stored dates as text.

Calculated field expression:

CASE
    WHEN UD01.ShortChar01 IS NULL THEN 'Blank'
    WHEN TRY_CONVERT(date, UD01.ShortChar01, 23) IS NULL THEN 'Invalid'
    ELSE 'Valid'
END

You could also use this kind of expression as part of a cleanup BAQ to identify bad rows before trying to convert or migrate the data.

Date Difference

Days between two dates:

DATEDIFF(day, OrderHed.OrderDate, OrderHed.NeedByDate)

Hours between two datetimes:

DATEDIFF(hour, LaborDtl.ClockInDateTime_c, LaborDtl.ClockOutDateTime_c)

Minutes between two datetimes:

DATEDIFF(minute, LaborDtl.ClockInDateTime_c, LaborDtl.ClockOutDateTime_c)

A more precise labor-hours-style expression:

DATEDIFF(minute, LaborDtl.ClockInDateTime_c, LaborDtl.ClockOutDateTime_c) / 60.0

The / 60.0 matters. If you use / 60, SQL may treat it as integer division depending on the expression.

Add Days, Hours, or Minutes

Add seven days:

DATEADD(day, 7, OrderHed.OrderDate)

Subtract thirty days:

DATEADD(day, -30, OrderHed.OrderDate)

Add two hours:

DATEADD(hour, 2, SomeTable.SomeDateTimeField)

Add fifteen minutes:

DATEADD(minute, 15, SomeTable.SomeDateTimeField)

This is useful in calculated fields, but also in BAQ criteria when comparing against relative dates.

Build a Date from Parts

Sometimes you have year, month, and day stored separately.

DATEFROMPARTS(SomeTable.YearNum, SomeTable.MonthNum, SomeTable.DayNum)

Example:

DATEFROMPARTS(FiscalPer.FiscalYear, FiscalPer.FiscalPeriod, 1)

Use this only when the pieces really are valid date parts. If the source values are questionable, validate them first.

Build a DateTime from Parts

DATETIME2FROMPARTS
(
    SomeTable.YearNum,
    SomeTable.MonthNum,
    SomeTable.DayNum,
    SomeTable.HourNum,
    SomeTable.MinuteNum,
    0,
    0,
    0
)

This is not common in normal BAQs, but it is useful when integrating ugly legacy data.

BAQ Criteria Examples

This is where users get tripped up the most.

Filtering a DateTime Field for One Day

This is the tempting pattern:

CAST(OrderHed.OrderDate AS date) = @TargetDate

It is simple, and it often works.

But it applies a function to the database column. That can make the query less efficient because SQL has to compute that expression against the field.

The better pattern is a range:

OrderHed.OrderDate >= @TargetDate

and:

OrderHed.OrderDate < DATEADD(day, 1, @TargetDate)

In BAQ Designer, that usually means two criteria rows:

Field Operation Value
OrderHed.OrderDate >= @TargetDate
OrderHed.OrderDate < DATEADD(day, 1, @TargetDate)

This gets every record on the target date, regardless of the time portion.

Date Range with Parameters

For a start date and end date:

OrderHed.OrderDate >= @StartDate

and:

OrderHed.OrderDate < DATEADD(day, 1, @EndDate)

This handles the full end date.

Be careful with this:

OrderHed.OrderDate <= @EndDate

If @EndDate is 2026-06-16, that usually means:

2026-06-16 00:00:00

So records later that day may not qualify.

This is one of the classic datetime bugs. The query looks right, but it drops records from the end date because the time portion was ignored.

Last 30 Days

If you want the last 30 days from the current moment:

OrderHed.OrderDate >= DATEADD(day, -30, GETDATE())

If you want from midnight 30 days ago:

OrderHed.OrderDate >= CAST(DATEADD(day, -30, GETDATE()) AS date)

Those are not the same thing.

The first one means 30 days ago at the current time.

The second one means the beginning of the day 30 days ago.

Today Only

For a datetime column, use a range.

Start of today:

OrderHed.OrderDate >= CAST(GETDATE() AS date)

Start of tomorrow:

OrderHed.OrderDate < DATEADD(day, 1, CAST(GETDATE() AS date))

Together, those return today’s records.

Avoid this pattern when you can:

CAST(OrderHed.OrderDate AS date) = CAST(GETDATE() AS date)

It is readable, but it wraps the database column in a function. The range version is usually the better habit.

This Month

Start of current month:

DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)

Start of next month:

DATEADD(month, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))

Criteria pattern:

OrderHed.OrderDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)

and:

OrderHed.OrderDate < DATEADD(month, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))

That gives you the current month without needing to care how many days are in the month.

BAQ CASE Expressions

Calculated fields often need to classify dates.

Past Due Flag

CASE
    WHEN OrderHed.NeedByDate < CAST(GETDATE() AS date) THEN 1
    ELSE 0
END

Past Due Text

CASE
    WHEN OrderHed.NeedByDate < CAST(GETDATE() AS date) THEN 'Past Due'
    WHEN OrderHed.NeedByDate = CAST(GETDATE() AS date) THEN 'Due Today'
    ELSE 'Future'
END

Null-Safe Date Display

CASE
    WHEN OrderHed.NeedByDate IS NULL THEN ''
    ELSE CONVERT(varchar(10), OrderHed.NeedByDate, 23)
END

Null-Safe Date Math

CASE
    WHEN OrderHed.OrderDate IS NULL OR OrderHed.NeedByDate IS NULL THEN NULL
    ELSE DATEDIFF(day, OrderHed.OrderDate, OrderHed.NeedByDate)
END

That kind of null handling matters in BAQs. A calculated field that works for 99% of rows but fails or misleads on the remaining 1% is still a problem.

C# and LINQ Equivalents

The C# side has the same conceptual problem. The names are different, but the same distinction matters.

C# Type Meaning
DateTime Date plus time, with a Kind value
DateTimeOffset Date plus time plus offset
DateOnly Date only
TimeOnly Time of day only
TimeSpan Duration, not a time-of-day

DateOnly and TimeOnly are the better semantic match when the value is truly date-only or time-only, but they are not available everywhere. If you are in older .NET Framework code, you are probably still dealing mostly with DateTime.

DateTime to DateOnly and TimeOnly

DateTime dt = new DateTime(2026, 6, 16, 14, 30, 45);

DateOnly d = DateOnly.FromDateTime(dt);
TimeOnly t = TimeOnly.FromDateTime(dt);

DateOnly plus TimeOnly to DateTime

DateOnly d = new DateOnly(2026, 6, 16);
TimeOnly t = new TimeOnly(14, 30, 45);

DateTime dt = d.ToDateTime(t);

DateTime to Formatted String

DateTime dt = new DateTime(2026, 6, 16, 14, 30, 45);

string dateOnly = dt.ToString("yyyy-MM-dd");
string timeOnly = dt.ToString("HH:mm:ss");
string dateTime = dt.ToString("yyyy-MM-ddTHH:mm:ss");

This is display logic. It creates strings. Do not do this early if you still need to sort, compare, filter, or perform date math.

String to DateTime

This works, but it depends on parsing rules:

string value = "2026-06-16T14:30:45";

DateTime dt = DateTime.Parse(value);

Better when you know the exact format:

using System.Globalization;

string value = "2026-06-16T14:30:45";

DateTime dt = DateTime.ParseExact(
    value,
    "yyyy-MM-ddTHH:mm:ss",
    CultureInfo.InvariantCulture
);

Best when the data might be bad:

using System.Globalization;

string value = "2026-06-16";

bool ok = DateTime.TryParseExact(
    value,
    "yyyy-MM-dd",
    CultureInfo.InvariantCulture,
    DateTimeStyles.None,
    out DateTime parsed
);

if (ok)
{
    // use parsed
}
else
{
    // handle bad input
}

Same concept as SQL TRY_CONVERT. Bad input should be handled intentionally.

LINQ Examples

Assume this shape:

var targetDate = new DateTime(2026, 6, 16);
var nextDate = targetDate.AddDays(1);

Filter a DateTime Column for a Specific Day

Avoid this when the query is going to SQL:

var rows = db.Orders
    .Where(o => o.OrderDate.Date == targetDate.Date);

It may translate, but it applies date conversion logic to the column.

Prefer the range pattern:

var rows = db.Orders
    .Where(o => o.OrderDate >= targetDate &&
                o.OrderDate < nextDate);

That is the same idea as the BAQ criteria pattern.

Keep the column clean. Put the boundary logic on the values.

Extract Date or Time in LINQ

var rows = db.Orders
    .Select(o => new
    {
        o.OrderNum,
        OrderDateOnly = o.OrderDate.Date,
        OrderTimeOnly = o.OrderDate.TimeOfDay
    });

This can be fine for projection or display, but I would still avoid using .Date casually in filters when a range comparison would be cleaner.

Add Days, Hours, or Minutes

var rows = db.Orders
    .Where(o => o.OrderDate >= DateTime.Today.AddDays(-30));

var rows = db.Jobs
    .Where(j => j.StartDateTime.AddHours(2) < DateTime.Now);

Use this when it represents the actual business rule. Be careful with DateTime.Now versus DateTime.Today versus UTC. They are not interchangeable.

DateOnly and TimeOnly in Newer Code

If your stack supports it, this is clearer:

DateOnly targetDate = new DateOnly(2026, 6, 16);

var rows = db.Orders
    .Where(o => o.RequiredDate == targetDate);

For time-only logic:

TimeOnly start = new TimeOnly(8, 0);
TimeOnly end = new TimeOnly(17, 0);

var rows = db.Shifts
    .Where(s => s.StartTime >= start &&
                s.StartTime < end);

This is more explicit than forcing everything through DateTime.

Practical Rules

1. Do not store dates as text unless you have no choice

Text dates always become somebody else’s parsing problem later.

If you inherit text dates, use defensive conversion:

TRY_CONVERT(date, UD01.ShortChar01, 23)

or in C#:

DateTime.TryParseExact(
    value,
    "yyyy-MM-dd",
    CultureInfo.InvariantCulture,
    DateTimeStyles.None,
    out DateTime parsed
);

2. Use the narrowest correct type

If it is only a date, use a date.

If it is only a time-of-day, use a time.

If it is a timestamp, use a datetime.

If the offset matters, use something that preserves the offset.

Do not collapse different meanings into one field just because it is convenient today. That is how small shortcuts become long-term data quality problems.

3. Use ISO-style text when text is unavoidable

Prefer:

yyyy-MM-dd

and:

yyyy-MM-ddTHH:mm:ss

Avoid ambiguous text like:

06/16/2026

That may look harmless, but regional formatting assumptions can turn harmless text into bad data.

4. For datetime filtering, use ranges

This is the most important BAQ pattern:

SomeDateTimeField >= @StartDate

and:

SomeDateTimeField < DATEADD(day, 1, @EndDate)

This is better than converting the column to a date inside the criteria.

5. Avoid formatting too early

This is fine for display:

CONVERT(varchar(10), OrderHed.OrderDate, 23)

But this is better for date behavior:

CAST(OrderHed.OrderDate AS date)

Once you convert a date to text, it is text. You gave up date sorting, date filtering, and date math.

6. Do not confuse time-of-day with duration

This is a time:

14:30

This is a duration:

14.5 hours

They are not the same concept.

In SQL, time is a time-of-day. For elapsed time, you usually calculate a difference:

DATEDIFF(minute, LaborDtl.ClockInDateTime_c, LaborDtl.ClockOutDateTime_c) / 60.0

7. Be explicit about local time, UTC, and offsets

If the value crosses users, plants, servers, integrations, APIs, or time zones, ambiguity becomes a defect.

A date on a report may be local business context.

A system event timestamp may need UTC.

A customer-facing appointment may need an offset.

Those are different problems. Treat them differently.

Final Thought

Dates are not hard because the syntax is hard.

They are hard because systems blur different concepts into one field.

A date-only value, a time-of-day, a timestamp, a duration, a formatted string, and a timezone-aware value are all different things.

Keep the meaning clean, and the conversions get much easier.

2

Final final thought.

Check the sargability of your queries.

If they are not it will impact performance.

Here is a useful link.