How do i format utc date time amd make sql queries

Question

1.00/5 (1 vote)

See more:

C#

DateTime utc = DateTime.UtcNow;
         string ab  =    utc.ToString("dd/MMM/yyyy/HH:00:00");
         DateTime utc1 = DateTime.UtcNow.AddHours(1);
         string ab1 = utc1.ToString("dd/MMM/yyyy/HH:00:00");

I need to make sql queries to take data between ab and ab1

What I have tried:

DateTime utc = DateTime.UtcNow;
string ab = utc.ToString("dd/MMM/yyyy/HH:00:00");
DateTime utc1 = DateTime.UtcNow.AddHours(1);
string ab1 = utc1.ToString("dd/MMM/yyyy/HH:00:00");

Posted 1-Mar-16 21:29pm

Member 12119075

Updated 2-Mar-16 1:08am

Add a Solution

Comments

John C Rayan 2-Mar-16 3:58am

You are saying you need sql query but I can't see any sql. Where is your sql ?

Richard Deeming 2-Mar-16 5:15am

Your code seems to imply that you're storing dates as strings. If so, you should change your database to store dates using one of the available date/time types instead. Not only will it be more efficient, it will make this sort of query much easier.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-03-02T01:08:00

You should always use unambiguous date formats with databases - this means that the date will interpreted correctly regardless of which localisation you are running the program in.

There are two ways you can format the dates e.g.

var ab = utc.ToString("yyyyMMddTHH:00:00");
var ab1 = utc1.ToString("u");

The first line results in "ISO unseparated date format"

20160302T11:00:00

and the second is "ISO 8601 datestamp format"

2016-03-02 12:55:18Z

To be clear, both of those dates represent the date "2nd March 2016".

The simplest way to "take data between ab and ab1" is to use

SQL

SELECT * FROM demoData WHERE datum BETWEEN @ab and @ab1

which is the same as saying

SQL

SELECT * FROM demoData WHERE datum >= @ab AND datum <= @ab1

Depending on your exact requirement you might need to get rid of one of the equal signs

SQL

SELECT * FROM demoData WHERE datum >= @ab AND datum < @ab1
-- OR
SELECT * FROM demoData WHERE datum > @ab AND datum <= @ab1

To run that query from C# use Parameterised queries[^] e.g.

C#

var sql = "SELECT * FROM demoData WHERE datum BETWEEN @ab and @ab1";
var command = new SqlCommand(sql);
command.Parameters.AddWithValue("@ab", ab);
command.Parameters.AddWithValue("@ab1", ab1);

Apart from helping to protect your database from SQL Injection, it also takes care of the single-quotes that are required around dates in the WHERE clause.

Take heed of the comment from @RichardDeeming - make sure these columns are the correct type - do not store dates in (n)char or (n)varchar columns, use the date or datetime type.