我如何格式化utc日期时间amd make sql查询 [英] How do i format utc date time amd make sql queries

问题描述

 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）; 

我需要进行sql查询才能获取数据ab和ab1之间



我尝试过：



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）;

解决方案

< blockquote>你应该总是对数据库使用明确的日期格式 - 这意味着无论你在哪个本地化程序运行，日期都会被正确解释。



有两个你可以格式化日期的方法，例如

 var ab = utc.ToString（yyyyMMddTHH：00：00）; 
 var ab1 = utc1.ToString（u）; 
 

第一行产生ISO未分离日期格式

 20160302T11：00：00 

，第二行是ISO 8601 datestamp格式

 2016-03-02 12：55：18Z 

需要说明的是，这两个日期代表的日期为2016年3月2日 。



在ab和ab1之间获取数据的最简单方法是使用

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

这与说

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

根据您的具体要求，您可能需要摆脱其中一个等号

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

要从C＃运行该查询，请使用参数化查询 [ ^ ]例如

  var  sql =   SELECT * FROM demoData WHERE datum  BETWEEN  @ab和@ ab1; 
  var  command =  new  SqlCommand（sql）; 
 command.Parameters.AddWithValue（  @ ab，ab）; 
 command.Parameters.AddWithValue（  @ ab1，ab1）; 

除了帮助保护您的数据库免受SQL注入之外，它还会处理WHERE子句中日期所需的单引号。



注意@RichardDeeming的注释 - 确保这些列的类型正确 - 不要将日期存储在（n）char或（n）varchar列中，使用日期或 datetime type。

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");

解决方案

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

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

which is the same as saying

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

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.

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.

这篇关于我如何格式化utc日期时间amd make sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！