如何将日期转换为 nvarchar

发布于 2024-12-15 08:15:34 字数 596 浏览 1 评论 0原文

在此处输入图像描述有一个搜索屏幕，我想通过搜索特定数据来显示结果。我的过滤器是“显示‘7/5/05’日期之后的所有日期”。

由于这是 JSF 表单，因此 7/5/05 被存储为 Date。并且再次将其转换为 Long 作为 '1120501800000'。

现在查询变成这样

SELECT * FROM   ABC  WHERE  due_date BETWEEN '1120501800000' AND '1952498291808'  ORDER  BY trstart_date DESC

但问题是 due_date 列是 "nvarchar(50)" ，并且值存储为“7/5/05”。

有人可以告诉我如何将“1120501800000”转换为“7/5/05”。即使我不知道什么是fastTime，如图所示？

提前致谢。

我正在使用 Jsf、Spring、IBatis、sql 服务器

原文

enter image description here
have a search screen and I want to show results by searching specific data.My Filter is "show all date after '7/5/05' date".

As this is a JSF form, so 7/5/05 gets stored as Date.And again this is getting converted to Long as '1120501800000'.

Now the query becomes like this

SELECT * FROM   ABC  WHERE  due_date BETWEEN '1120501800000' AND '1952498291808'  ORDER  BY trstart_date DESC

But the problem is that due_date column is of "nvarchar(50)" , and values are stored as "7/5/05".

Can someone please tell me how can I convert "1120501800000" to "7/5/05".Even I dont know what is fastTime as shown in image?

Thanks in advance.

I am using Jsf,Spring,IBatis,sql server

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

虫児飞 2024-12-22 08:15:34

这些数字似乎代表了 unix 时间戳，具有毫秒精度。

SELECT DATEADD( SECOND
              , CONVERT(BIGINT, '1120501800000') / 1000
              , '1970/01/01')

这将转换为 2005/07/04 (yyyy/mm/dd) 的 18:30。

在您的示例中，您得到 2005/07/05，这可能是由于您当地时间的 UTC 时区偏移所致。假设您提前 6 小时；翻译为：

SELECT DATEADD( HOUR
              , 6
              , DATEADD( SECOND
                       , CONVERT(BIGINT, '1120501800000') / 1000
                       , '1970/01/01')
              )

如您所料，这将转换为 2005/07/05。

要获得您假装的 mm/dd/yy 格式：

SELECT CONVERT(VARCHAR(2), DATEPART(MONTH, date)) + '/'
     + CONVERT(VARCHAR(2), DATEPART(DAY, date))   + '/'
     + RIGHT(CONVERT(VARCHAR(4), DATEPART(YEAR, date)), 2)
  FROM ( SELECT DATEADD(HOUR, 6, DATEADD(SECOND, CONVERT(BIGINT, '1120501800000') / 1000, '1970/01/01'))
       ) AS T (date)

Those numbers seem to represent unix timestamps, with millisecond precision.

SELECT DATEADD( SECOND
              , CONVERT(BIGINT, '1120501800000') / 1000
              , '1970/01/01')

This converts to 2005/07/04 (yyyy/mm/dd), at 18:30.

In your example, you get 2005/07/05, which may be due to a UTC timezone offset in your local time. Let's say you're 6 hours ahead; that translates to:

SELECT DATEADD( HOUR
              , 6
              , DATEADD( SECOND
                       , CONVERT(BIGINT, '1120501800000') / 1000
                       , '1970/01/01')
              )

This converts to 2005/07/05, as you would expect.

To get the mm/dd/yy formatting you pretend:

SELECT CONVERT(VARCHAR(2), DATEPART(MONTH, date)) + '/'
     + CONVERT(VARCHAR(2), DATEPART(DAY, date))   + '/'
     + RIGHT(CONVERT(VARCHAR(4), DATEPART(YEAR, date)), 2)
  FROM ( SELECT DATEADD(HOUR, 6, DATEADD(SECOND, CONVERT(BIGINT, '1120501800000') / 1000, '1970/01/01'))
       ) AS T (date)

回复收藏 0 原文

~没有更多了~