如何使用Sql仅选择Datetime的日期部分？ [英] How Do I Select Only The Date Part Of Datetime With Sql?
问题描述
我试图只选择出生日期的日期部分，其中还包括我在VS2013内建立的网页中显示的时间。
我的SQL包程序工作正常，除了我打电话给出生日期我还得到了我不想要的时间。
我已经阅读了很多关于'CONVERT，TRUNC，CAST，GETDATE的页面，但我对如何在我的代码中使用这些内容感到不知所措。我不确定哪种方法最好或者有更简单的方法。
任何帮助都会非常感激。
谢谢
我的SQL包代码......
程序user_search
（
get_user_details_dll OUT SYS_REFCURSOR，
p_loginid_search IN varchar2，
p_forename_search IN varchar2，
p_surname_search IN varchar2，
p_enumber_search IN varchar2，
p_pnumber_search IN varchar2
）
IS
l_loginid_clause VARCHAR2（255）;
l_forename_clause VARCHAR2（255）;
l_surname_clause VARCHAR2（255） ;
l_enumber_clause VARCHAR2（255）;
l_pnumber_clause VARCHAR2（255）;
BEGIN
如果p_loginid_search不是NULL那么
l_loginid_clause：='AND LOWER（LOGIN_ID）='''|| LOWER（p_loginid_search）||''';
END IF;
如果p_forename_search不是NULL那么
l_forename_clause：='AND LOWER（FORENAME）='''|| LOWER（p_forename_search）||''';
END IF;
如果p_surname_search不是NULL那么
l_surname_clause：='AND LOWER（SURNAME）='''|| LOWER（p_surname_search）||'''';
END IF;
如果p_enumber_search不是NULL那么
l_enumber_clause：='AND LOWER（STAFF_ID）='''|| LOWER（p_enumber_search）||''';
END IF;
如果p_pnumber_search不是NULL那么
l_pnumber_clause：='AND GC_NO ='''|| p_pnumber_search ||''';
结束IF;
OPEN get_user_details_dll FOR
'SELECT login_id， staff_id，姓氏，forename，middlename，date_of_birth，gc_no，位置，位置，网站，评论'||
'FROM users_dev'||
'WHERE 1 = 1'||
l_loginid_clause ||
l_forename_clause ||
l_surname_clause ||
l_enumber_clause ||
l_pnumber_clause ||
'ORDER BY surname';
END user_search;
Hi,
I am trying to select only the date part of a date of birth that also includes the time when shown within my webpage i am building within VS2013.
My SQL package procedure works fine apart from when i call for the date of birth i get the time back also which i do not want.
I have read a lot of pages about 'CONVERT, TRUNC, CAST, GETDATE but i am overwhelmed by how to use these within my code. I am not sure which is the best to use or if there is a simpler way of doing it.
Any help would be much appreciated.
Thank you
My SQL Package Code...
procedure user_search
(
get_user_details_dll OUT SYS_REFCURSOR,
p_loginid_search IN varchar2,
p_forename_search IN varchar2,
p_surname_search IN varchar2,
p_enumber_search IN varchar2,
p_pnumber_search IN varchar2
)
IS
l_loginid_clause VARCHAR2(255);
l_forename_clause VARCHAR2(255);
l_surname_clause VARCHAR2(255);
l_enumber_clause VARCHAR2(255);
l_pnumber_clause VARCHAR2(255);
BEGIN
IF p_loginid_search IS NOT NULL THEN
l_loginid_clause := ' AND LOWER(LOGIN_ID) =''' || LOWER(p_loginid_search) ||'''';
END IF;
IF p_forename_search IS NOT NULL THEN
l_forename_clause := ' AND LOWER(FORENAME) =''' || LOWER(p_forename_search) ||'''';
END IF;
IF p_surname_search IS NOT NULL THEN
l_surname_clause := ' AND LOWER(SURNAME) =''' || LOWER(p_surname_search) ||'''';
END IF;
IF p_enumber_search IS NOT NULL THEN
l_enumber_clause := ' AND LOWER(STAFF_ID) =''' || LOWER(p_enumber_search) ||'''';
END IF;
IF p_pnumber_search IS NOT NULL THEN
l_pnumber_clause := ' AND GC_NO =''' || p_pnumber_search ||'''';
END IF;
OPEN get_user_details_dll FOR
'SELECT login_id, staff_id, surname, forename, middlename, date_of_birth, gc_no, position, location, site, comments ' ||
'FROM users_dev ' ||
' WHERE 1 = 1 ' ||
l_loginid_clause ||
l_forename_clause ||
l_surname_clause ||
l_enumber_clause ||
l_pnumber_clause ||
'ORDER BY surname ';
END user_search;
推荐答案
您可以使用DataFormatString或类似属性在客户端应用格式，具体取决于您使用它来显示它的控件。
或者，在您的SELECT语句中，您可以使用兑换。如果你有sql2008或更高版本就很容易，因为你可以做到这一点
You can apply formatting on the client side using the DataFormatString or similar property depending on what control your using to show it.
Or, in your SELECT statement you can use convert. If you have sql2008 or higher it's easy because you can just do
SELECT CONVERT(DATE, date_of_birth) AS date_of_birth, ...
这将删除时间部分。
当然，您始终可以将列存储为日期而不是DateTime如果你有sql 2008或更高版本。
and that will remove the time portion.
Of course you can always store the column as just Date instead of DateTime if you have sql 2008 or later.
你必须学会区分数据和它的表示...
当你选择一个提交的SQL您可以选择以不同方式呈现日期的价值...
第一天或第一天，2位数年份或4位数年份，包括星期几，按名称划分的月份而不是数字......
所有这些都不会改变价值...
所以让SQL按原样带来值并在UI代码中执行请求的格式...
确切的选择s格式可能会有所不同，具体取决于您使用的UI元素，但要获得品味...
http://msdn.microsoft.com/en-us/library/zdtaw1bw(v=vs.110).aspx [ ^ ]
You have to learn to distinguish between the data and its presentation...
When you pick an SQL filed with the value of a date you can choose to present it in different ways...
Day first or month first, 2 digit year or four digit year, including day of week, the month by name and not number...
All these will not change the value at all...
So let the SQL bring the value as is and do the requested formatting in the UI code...
The exact options to do the formatting can differ depending on the UI elements you are using, but to get the taste...
http://msdn.microsoft.com/en-us/library/zdtaw1bw(v=vs.110).aspx[^]
这篇关于如何使用Sql仅选择Datetime的日期部分？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
