formatting date while querying a db

Question

formatting date while querying a db

gal mor 141

I want to run certain queries depends on my values but one date entry is different from the other one so condition cant be true..
what should I do?

Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2022-10-24T13:42:18.697+00:00

Your question is ambiguous, please clarify as all I see is one value has no time while the other does.
gal mor 141 Reputation points

2022-10-24T14:55:26.037+00:00

It shows clearly in both in the yellow lines what values are being compared
Yitzhak Khabinsky 26,586 Reputation points

2022-10-24T15:50:03.567+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

All within the question, no images.
gal mor 141 Reputation points

2022-10-24T16:06:31.277+00:00

Don't know how else can I ask this. But my problem is that I try to compare date value that I got from viewgrid cell to a new one I tried to update into. The old one I was trying to update was caught from viewgrid cell and is displaying date with time while the other one ( the new one I'm trying to change into ) is being caught as just date. Ofc if I show you dB queries it will show u the same. One query with time the other without. Now the question is how can I change that ? I provided my code behind tho so idk what help will it give if I told my dB version

Accepted answer

2 additional answers

Your answer

Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2022-10-24T13:42:18.697+00:00

Your question is ambiguous, please clarify as all I see is one value has no time while the other does.
gal mor 141 Reputation points

2022-10-24T14:55:26.037+00:00

It shows clearly in both in the yellow lines what values are being compared
Yitzhak Khabinsky 26,586 Reputation points

2022-10-24T15:50:03.567+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

All within the question, no images.
gal mor 141 Reputation points

2022-10-24T16:06:31.277+00:00

Don't know how else can I ask this. But my problem is that I try to compare date value that I got from viewgrid cell to a new one I tried to update into. The old one I was trying to update was caught from viewgrid cell and is displaying date with time while the other one ( the new one I'm trying to change into ) is being caught as just date. Ofc if I show you dB queries it will show u the same. One query with time the other without. Now the question is how can I change that ? I provided my code behind tho so idk what help will it give if I told my dB version

Answer 1

Lan Huang-MSFT 30,186 Microsoft External Staff

Hi @gal mor ,
The date format of your database should be DateTime, you'd better set the date format to DateTime, not string.

DateTime oldDate =Convert.ToDateTime( dt.Rows[e.RowIndex].ItemArray[2]);       
DateTime DATE =Convert.ToDateTime(created.Text);

Best regards,
Lan Huang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

gal mor 141 Reputation points

2022-10-25T06:25:35.693+00:00

Well this was helpful altho it didnt solve my issue .
I ended up doing this:
var oldCreated = Convert.ToDateTime(dt.Rows[e.RowIndex].ItemArray[2]).ToShortDateString();
var DATE = Convert.ToDateTime(created.Text).ToShortDateString();

as when I did what you said it was catching the dates with '00:00:00', and the db query didnt like it.
also I changed to var because Datetime didnt have the prop of ToShortDateString();
THANKS!!
Lan Huang-MSFT 30,186 Reputation points Microsoft External Staff

2022-10-25T06:57:38.6+00:00

Hi @gal mor ,
Glad you solved the problem, if possible maybe you can accept an answer you think is helpful, or post the answer yourself and accept the answer to close the post.

Answer 2

AgaveJoe 30,126

It appears the problem is comparing two completely different string representations of a date; 27-10-2022 and 27/10/2022 00:00:00. Either convert the strings to actual DateTime types and compare the datetime types or apply the same string format to each date. I recommend converting the date strings to a DateTime.

Parse date and time strings in .NET

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-10-24T21:46:35.59+00:00

And overall using strings for date and time rather than dedicated datatypes is a bad idea, both in .NET and in SQL: It will only lead you into trouble.

Answer 3

Olaf Helper 47,436

but one date entry is different from the other

That are no dates, but strings and they have different content, so what do you expect as result?
Use type safe programming instead all as string.

Share via

formatting date while querying a db

2 additional answers

Your answer