Convert empty DateTime from 01.01.1900 in SQL to null in C# and vice versa2019 Community Moderator ElectionConvert a string to an enum in C#How can I convert a Unix timestamp to DateTime and vice versa?How do you convert a byte array to a hexadecimal string, and vice versa?Converting a String to DateTimeGet int value from enum in C#Converting XDocument to XmlDocument and vice versaC# DateTime to “YYYYMMDDHHMMSS” formatGetting the Average of a Calculated Item With Nullable DateTimec# (3.5) use TableAdapters to read/write null DateTime values to a databaseHow to convert a nullable datetime value to string using data reader?
Hacking a Safe Lock after 3 tries
Interplanetary conflict, some disease destroys the ability to understand or appreciate music
How to make healing in an exploration game interesting
AG Cluster db upgrade by vendor
Instead of Universal Basic Income, why not Universal Basic NEEDS?
What should tie a collection of short-stories together?
A limit with limit zero everywhere must be zero somewhere
How to deal with taxi scam when on vacation?
Define, (actually define) the "stability" and "energy" of a compound
Do I need to be arrogant to get ahead?
Stiffness of a cantilever beam
My Graph Theory Students
The difference between「N分で」and「後N分で」
Sailing the cryptic seas
Life insurance that covers only simultaneous/dual deaths
How to create the Curved texte?
Official degrees of earth’s rotation per day
What approach do we need to follow for projects without a test environment?
Do the common programs (for example: "ls", "cat") in Linux and BSD come from the same source code?
If curse and magic is two sides of the same coin, why the former is forbidden?
Employee lack of ownership
A sequence that has integer values for prime indexes only:
Charles Hockett - 'F' article?
What's the meaning of “spike” in the context of “adrenaline spike”?
Convert empty DateTime from 01.01.1900 in SQL to null in C# and vice versa
2019 Community Moderator ElectionConvert a string to an enum in C#How can I convert a Unix timestamp to DateTime and vice versa?How do you convert a byte array to a hexadecimal string, and vice versa?Converting a String to DateTimeGet int value from enum in C#Converting XDocument to XmlDocument and vice versaC# DateTime to “YYYYMMDDHHMMSS” formatGetting the Average of a Calculated Item With Nullable DateTimec# (3.5) use TableAdapters to read/write null DateTime values to a databaseHow to convert a nullable datetime value to string using data reader?
Our legacy database stores empty Date values as 01.01.1900
I want my application to use 'null' as empty value everywhere by using Converters.
But documentation says that:
A null value will never be passed to a value converter. This makes the implementation of conversions easier and allows them to be shared amongst nullable and non-nullable properties.
My code:
var dateConverter = new ValueConverter<DateTime?, DateTime>(
v => v == null ? new DateTime(1900, 01, 01) : v.Value,
v => v.Year == 1900 ? (DateTime?)null : v);
modelBuilder.Entity<Table>().Property(a => a.Date).HasConversion(dateConverter);
//query
return table.Where(a => a.Date == null);
Is translated to:
select * from table a where a.Date is null
I need it to automatically translate to something like:
select * from table a where a.Date = '1900-01-01T00:00:00.000'
Is there any workaround for null values in Converters?
c# linq entity-framework-core ef-core-2.2
edited Mar 7 at 14:03
Wai Ha Lee
6,046124065
asked Mar 7 at 13:59
xietyxiety
3329
|
show 20 more comments
Our legacy database stores empty Date values as 01.01.1900
I want my application to use 'null' as empty value everywhere by using Converters.
But documentation says that:
A null value will never be passed to a value converter. This makes the implementation of conversions easier and allows them to be shared amongst nullable and non-nullable properties.
My code:
var dateConverter = new ValueConverter<DateTime?, DateTime>(
v => v == null ? new DateTime(1900, 01, 01) : v.Value,
v => v.Year == 1900 ? (DateTime?)null : v);
modelBuilder.Entity<Table>().Property(a => a.Date).HasConversion(dateConverter);
//query
return table.Where(a => a.Date == null);
Is translated to:
select * from table a where a.Date is null
I need it to automatically translate to something like:
select * from table a where a.Date = '1900-01-01T00:00:00.000'
Is there any workaround for null values in Converters?
c# linq entity-framework-core ef-core-2.2
edited Mar 7 at 14:03
Wai Ha Lee
6,046124065
asked Mar 7 at 13:59
xietyxiety
3329
1
That's neither empty nor null. That's a perfectly valid DateTime either in the SQL Server or C#
– Panagiotis Kanavos
Mar 7 at 14:01
2
why do you use this as a special value? why not make the column nullable which is a non-breaking change
– Daniel A. White
Mar 7 at 14:02
Why are you trying to change a valid value intonull? Where did this value come from? Some table field default? Perhaps that field should be nullable instead of accepting meaningless defaults?
– Panagiotis Kanavos
Mar 7 at 14:02
1
Our legacy database stores empty Date values as 01.01.1900. I can not change this fact.
– xiety
Mar 7 at 14:04
1
@xiety instead of trying to change how SQL works (and having to deal with confusion and bugs when even you forget it's changed behind the scenes), define anEmptydate somewhere, eg anAX.Emptyconstant, and use that for comparisons.
– Panagiotis Kanavos
Mar 8 at 8:12
|
show 20 more comments
Our legacy database stores empty Date values as 01.01.1900
I want my application to use 'null' as empty value everywhere by using Converters.
But documentation says that:
A null value will never be passed to a value converter. This makes the implementation of conversions easier and allows them to be shared amongst nullable and non-nullable properties.
My code:
var dateConverter = new ValueConverter<DateTime?, DateTime>(
v => v == null ? new DateTime(1900, 01, 01) : v.Value,
v => v.Year == 1900 ? (DateTime?)null : v);
modelBuilder.Entity<Table>().Property(a => a.Date).HasConversion(dateConverter);
//query
return table.Where(a => a.Date == null);
Is translated to:
select * from table a where a.Date is null
I need it to automatically translate to something like:
select * from table a where a.Date = '1900-01-01T00:00:00.000'
Is there any workaround for null values in Converters?
c# linq entity-framework-core ef-core-2.2
edited Mar 7 at 14:03
Wai Ha Lee
6,046124065
asked Mar 7 at 13:59
xietyxiety
3329
Our legacy database stores empty Date values as 01.01.1900
I want my application to use 'null' as empty value everywhere by using Converters.
But documentation says that:
A null value will never be passed to a value converter. This makes the implementation of conversions easier and allows them to be shared amongst nullable and non-nullable properties.
My code:
var dateConverter = new ValueConverter<DateTime?, DateTime>(
v => v == null ? new DateTime(1900, 01, 01) : v.Value,
v => v.Year == 1900 ? (DateTime?)null : v);
modelBuilder.Entity<Table>().Property(a => a.Date).HasConversion(dateConverter);
//query
return table.Where(a => a.Date == null);
Is translated to:
select * from table a where a.Date is null
I need it to automatically translate to something like:
select * from table a where a.Date = '1900-01-01T00:00:00.000'
Is there any workaround for null values in Converters?
c# linq entity-framework-core ef-core-2.2
c# linq entity-framework-core ef-core-2.2
edited Mar 7 at 14:03
Wai Ha Lee
6,046124065
asked Mar 7 at 13:59
xietyxiety
3329
edited Mar 7 at 14:03
Wai Ha Lee
6,046124065
asked Mar 7 at 13:59
xietyxiety
3329
edited Mar 7 at 14:03
Wai Ha Lee
6,046124065
edited Mar 7 at 14:03
Wai Ha Lee
6,046124065
edited Mar 7 at 14:03
Wai Ha Lee
6,046124065
6,046124065
asked Mar 7 at 13:59
xietyxiety
3329
asked Mar 7 at 13:59
xietyxiety
3329
asked Mar 7 at 13:59
xietyxiety
3329
3329
1
That's neither empty nor null. That's a perfectly valid DateTime either in the SQL Server or C#
– Panagiotis Kanavos
Mar 7 at 14:01
2
why do you use this as a special value? why not make the column nullable which is a non-breaking change
– Daniel A. White
Mar 7 at 14:02
Why are you trying to change a valid value intonull? Where did this value come from? Some table field default? Perhaps that field should be nullable instead of accepting meaningless defaults?
– Panagiotis Kanavos
Mar 7 at 14:02
1
Our legacy database stores empty Date values as 01.01.1900. I can not change this fact.
– xiety
Mar 7 at 14:04
1
@xiety instead of trying to change how SQL works (and having to deal with confusion and bugs when even you forget it's changed behind the scenes), define anEmptydate somewhere, eg anAX.Emptyconstant, and use that for comparisons.
– Panagiotis Kanavos
Mar 8 at 8:12
|
show 20 more comments
1
That's neither empty nor null. That's a perfectly valid DateTime either in the SQL Server or C#
– Panagiotis Kanavos
Mar 7 at 14:01
2
why do you use this as a special value? why not make the column nullable which is a non-breaking change
– Daniel A. White
Mar 7 at 14:02
Why are you trying to change a valid value intonull? Where did this value come from? Some table field default? Perhaps that field should be nullable instead of accepting meaningless defaults?
– Panagiotis Kanavos
Mar 7 at 14:02
1
Our legacy database stores empty Date values as 01.01.1900. I can not change this fact.
– xiety
Mar 7 at 14:04
1
@xiety instead of trying to change how SQL works (and having to deal with confusion and bugs when even you forget it's changed behind the scenes), define anEmptydate somewhere, eg anAX.Emptyconstant, and use that for comparisons.
– Panagiotis Kanavos
Mar 8 at 8:12
1
1
That's neither empty nor null. That's a perfectly valid DateTime either in the SQL Server or C#
– Panagiotis Kanavos
Mar 7 at 14:01
That's neither empty nor null. That's a perfectly valid DateTime either in the SQL Server or C#
– Panagiotis Kanavos
Mar 7 at 14:01
2
2
why do you use this as a special value? why not make the column nullable which is a non-breaking change
– Daniel A. White
Mar 7 at 14:02
why do you use this as a special value? why not make the column nullable which is a non-breaking change
– Daniel A. White
Mar 7 at 14:02
Why are you trying to change a valid value into
null? Where did this value come from? Some table field default? Perhaps that field should be nullable instead of accepting meaningless defaults?– Panagiotis Kanavos
Mar 7 at 14:02
Why are you trying to change a valid value into
null? Where did this value come from? Some table field default? Perhaps that field should be nullable instead of accepting meaningless defaults?– Panagiotis Kanavos
Mar 7 at 14:02
1
1
Our legacy database stores empty Date values as 01.01.1900. I can not change this fact.
– xiety
Mar 7 at 14:04
Our legacy database stores empty Date values as 01.01.1900. I can not change this fact.
– xiety
Mar 7 at 14:04
1
1
@xiety instead of trying to change how SQL works (and having to deal with confusion and bugs when even you forget it's changed behind the scenes), define an
Empty date somewhere, eg an AX.Empty constant, and use that for comparisons.– Panagiotis Kanavos
Mar 8 at 8:12
@xiety instead of trying to change how SQL works (and having to deal with confusion and bugs when even you forget it's changed behind the scenes), define an
Empty date somewhere, eg an AX.Empty constant, and use that for comparisons.– Panagiotis Kanavos
Mar 8 at 8:12
|
show 20 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55045549%2fconvert-empty-datetime-from-01-01-1900-in-sql-to-null-in-c-sharp-and-vice-versa%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55045549%2fconvert-empty-datetime-from-01-01-1900-in-sql-to-null-in-c-sharp-and-vice-versa%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
That's neither empty nor null. That's a perfectly valid DateTime either in the SQL Server or C#
– Panagiotis Kanavos
Mar 7 at 14:01
2
why do you use this as a special value? why not make the column nullable which is a non-breaking change
– Daniel A. White
Mar 7 at 14:02
Why are you trying to change a valid value into
null? Where did this value come from? Some table field default? Perhaps that field should be nullable instead of accepting meaningless defaults?– Panagiotis Kanavos
Mar 7 at 14:02
1
Our legacy database stores empty Date values as 01.01.1900. I can not change this fact.
– xiety
Mar 7 at 14:04
1
@xiety instead of trying to change how SQL works (and having to deal with confusion and bugs when even you forget it's changed behind the scenes), define an
Emptydate somewhere, eg anAX.Emptyconstant, and use that for comparisons.– Panagiotis Kanavos
Mar 8 at 8:12