如何在sql server中使用日期和时间对细节进行排序? [英] How to sort details with Date and time in sql server ?

查看:99
本文介绍了如何在sql server中使用日期和时间对细节进行排序?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

Hai

如何使用日期和时间对详细信息asc或desc进行排序,我将使用24小时格式并在sql server.i中捕获。需要使用日期和时间按升序或降序对详细信息进行排序。



ID - 1362

用户 - 管理员

日期 - 2013-12-10

时间 - 15:33:06'这是PM

部分 - 登录

Actoin - 登录

描述 - 管理员用户成功登录

DaTi - 10-12-2013 3:33:06 PM





ID - 13624

用户 - 演示1

日期 - 2013-12-10

时间 - 03:35 :21'这是AM

部分 - 退出

行动 - 退出

描述 - demo1用户成功注销

DaTi - NULL



上面的详细信息是我的sql server中的两条记录



上面的一个需要使用日期和时间进行排序。

在selete查询中我会喜欢按日期排序DESC,时间DESC

i我使用DESC所以我得到我首先得到一个,但是我得到PM时间第一个和第二个,但实际上AM是第一个,PM是第二个。所以如何使用日期和时间(24小时格式)对细节进行排序。



注意:需要同时使用日期和时间进行排序。



其实我在vb.net中单独获取当前日期和时间并存储在sql server

日期作为日期数据类型

时间为时间(7)数据类型。



如果需要获取两者一样,如何从vb.net获取时间和sql server中的表列需要提供什么数据类型。

请回复asap

问候

AravindB

Hai
How to sort details asc or desc using date and time,i will use 24hrs format and capture in sql server.i need to sort details using Date and Time for Ascending or descending order.

ID--1362
User--Admin
Date--2013-12-10
Time--15:33:06 'This is PM
Section--LogIn
Actoin--Sign In
Description--Admin user login successfully
DaTi--10-12-2013 3:33:06 PM


ID--13624
User--demo1
Date--2013-12-10
Time--03:35:21 'This is AM
Section--Log Out
Action--Sign Out
Description--demo1 user logout successfully
DaTi--NULL

the above details is two records in my sql server

The above one is need to sort using Date and Time both.
In selete query i will like "order by Date DESC,Time DESC"
i am use DESC so i get lates one at first,but what i get PM time first and AM second,but actually AM is first and PM is Second.so how to sort Details with Date and Time(24hrs format).

note:need to use both Date and Time for sorting.

Actually i am get current date and time separately in vb.net and store in sql server
Date as Date datatype
Time as Time(7) datatype.

if need to get both as same ,how to get timedate from vb.net and what datatype need to give for table column in sql server.
Pls reply asap
Regards
AravindB

推荐答案

15:33:06不是AM - 它是PM。具体来说,下午3点33分......



看起来你要分别存储你的日期和时间值而且字符串为s-这很糟糕想法,如果你想对它们进行任何排序或比较 - 虽然它可能非常笨拙,并要求你先将它转换为DateTime值。



那么为什么不将日期和时间值存储在一起,作为单个DateTime对象?这样,您的排序和比较就变成:

15:33:06 is not AM - it is PM. Specifically, it is 3:33 in the afternoon...

It looks like you are storing your date and time values separately and as string s- this is a bad idea if you want to do any sorting or comparison on them - though it is possible it is very clumsy, and requires you to convert it to a DateTime value first.

So why not store your Date and Time values together, as a single DateTime object? That way, your sorting and comparisons just become:
SELECT * FROM MyTable ORDER BY DateTimeColumnName ASC

如果出于特定原因只需要日期部分,只需在需要时提取它就更简单了。添加时间戳值变得简单得多,因为你可以使用GetDate(SQL)或DateTime.Now(.NET)

If you need just the date portion for a specific reason, it is simpler to extract just that only when you need it. And it becomes a lot simpler to add your time stamp value as you can just use GetDate (SQL) or DateTime.Now (.NET)


作为你的帖子我已经明白你想要约会部分处于降序模式,特定日期您希望时间处于升序模式,因此您可以尝试使用此sorintg。



从日期DESC中选择表格顺序,时间asc



所以这将解决你的问题
As your post i have understood that you want date portion is in descending mode and particular date you want time in ascending mode so you can try using this sorintg.

select * from table order by Date DESC,Time asc

So this will solve your problem


这篇关于如何在sql server中使用日期和时间对细节进行排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆