如果nvarchar中的列可以从日期时间获取小时 [英] Is it possible to get hour from datetime if the column in nvarchar

问题描述

是否可以通过使用DatePart()方法从日期时间获取小时，而日期在nvarchar中.
我正在尝试获取但出现如下错误:
消息241，级别16，状态1，第14行
从字符串转换日期和/或时间时转换失败.

示例查询是:

Hi,

Is it possible by using DatePart() method to get hours from datetime where date is in nvarchar.
I am trying to get that but getting an error like:
Msg 241, Level 16, State 1, Line 14
Conversion failed when converting date and/or time from character string.

the sample query is:

Declare @Hours Table (Hour int);
 
Declare @i as int;
Set @i = 0
While @i < 24
Begin
   Insert into @Hours
   Values(@i);
 
   Set @i = @i + 1;
End;
 
 
Select
   [Hour of Day],
   Count([Total Calls]) as total
From
(
   SELECT
        DATEPART(HOUR, convert(Datetime,ShipDate,13)) AS [Hour of Day]
      , Id AS [Total Calls]
   FROM ShipmentDetail
   Union ALL
   SELECT Hour, NULL
   From @Hours
) as Data
 
GROUP BY [Hour of Day]
ORDER BY [Hour of Day]

请修改查询.

谢谢与问候，
Raghu.

please modify the query.

Thanks&Regards,
Raghu.

推荐答案

如果您具有varchar数据类型来存储数据，则需要解析日期并将其秘密转换为DateTime，然后可以从datetime中获取所需的部分.

喜欢，

Hi,

If you have varchar datatype to store your data, you need to parse your date and covert it into DateTime and then you can get your desired part from the datetime.

like,

DateTime myDateTime = DateTime.Parse("yourstringdate");
int myDay = myDateTime.Day;

在这里，我使用了Parse方法，但是可以使用TryParse来使代码的错误概率降低.

希望这些信息对您有帮助，

谢谢
-Amit Gajjar.

Here i have used Parse method but instead you can use TryParse to make your code with less error probability.

Hope this information helps you,

Thanks
-Amit Gajjar.

不，不是真的.

问题在于，并非所有区域性都以相同的方式表示日期和时间，因此，如果不确切知道用​​户将日期时间值存储在数据库中时使用的格式，就无法设计一种简单，可靠的方式来提取日期或来自字符串的时间信息.当然可以，但可能不可行，因为选择的数量和特殊情况可能令人望而却步.

更好的解决方案是根本不将日期或时间存储为字符串，而应将它们尽快转换为DateTime值，然后存储它们.这样，您很有可能从用户特定的本地格式转换为Datetime，而不必再担心它.

No, not really.

The problem is that not all cultures represent dates and times the same way, so without knowing exactly what format your user was using when they stored the date time value in the database, you can''t design a simple, reliable way to extract date or time information from a string. It is possible, certainly, but it may not be practical, as the number of options and special cases may be prohibitive.

A much, much better solution is to not store dates or times as strings at all, but to convert them to DateTime values as soon as possible, and store those instead. That way, you stand a good chance of converting from a user-specific local format to a Datetime and never have to worry about it again.

是的，但是您应该在以下格式
yyyy-dd-MM hh:mm:ss
转换为日期时间时

和
yyyy-MM-dd hh:mm:ss
与日期时间字段进行比较时

祝您编程愉快！
:)

yes but you should have data in nvarchar field in below format
yyyy-dd-MM hh:mm:ss
when converting to datetime

and
yyyy-MM-dd hh:mm:ss
when comparing with date-time field

Happy coding!
:)

这篇关于如果nvarchar中的列可以从日期时间获取小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！