问题描述
我试图只选择出生日期的日期部分，其中还包括我在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;
推荐答案
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仅选择Datetime的日期部分？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！