GETDATE but not the Time

[debbie1212]

I have a date field that is added with the current date using GETDATE. However I would like for it to add only the date and not the time. I found information in this site on how to select only the date when the date and time are in the field but I can't get the code right because of the existing code in the stored procedure. Besides, I would rather just have the date in the field than the date and time.

These are the two stored procedures.

This is the one that stores the date in the field:

CREATE PROCEDURE FolderRequest_Add
@intReq_Folder_ID int,
@datReq_Date datetime,
@strReq_User char(5),
@strReq_Action char(10),
@intReq_From_Loc int,
@intReq_To_Loc int
AS
INSERT into WDtRequest
(req_folder_id,
req_date,
req_user,
req_action,
req_from_loc,
req_to_loc
)
VALUES
(@intReq_Folder_ID,
GETDATE(),
@strReq_User,
'Request',
@intReq_From_Loc,
@intReq_To_Loc

This is the stored procedure that is retrieving the records by date:

CREATE PROCEDURE FolderRequest_GetByReqDate
@datReq_Date as datetime,
@parm_StartRow as int = '0', --The current start row to begin the search.
@parm_EndRow as int = '50' --The end row for the search.
AS
--Other variables to use in the search...
DECLARE @TOTALCOUNT AS INT
DECLARE @RECORDCOUNT AS INT
--declare @loc_SortOrder as varchar(3) --This is the sort order.
--declare @totalcount as int
--Create a table to hold the values of the Folder ID that are found to display on the search results screen.
--This will allow for the paging through of the records.
CREATE TABLE #loc_tKey
(
RowID int IDENTITY (1, 1) NOT NULL, --This is an auto--increment field for paging.
FolderID int NOT NULL --This is the same as the folder ID field.
)
BEGIN
INSERT INTO #loc_tKey
(
FolderID
)
-- SELECT TOP 1000
SELECT Distinct TOP 1000
WDtRequest.REQ_FOLDER_ID
FROM
WDtRequest
WITH (NOLOCK)
WHERE
REQ_DATE = @datReq_Date AND
REQ_ACTION = 'Request' AND
REQ_DATE = (SELECT MAX(REQ_DATE) FROM WDtRequest WDtRequest2
WHERE REQ_DATE = @datReq_Date AND
WDtRequest.REQ_FOLDER_ID = WDtRequest2.REQ_FOLDER_ID)

-- ORDER BY REQ_DATE
OPTION(FAST 1000)
END

---This will be the second query....
BEGIN
SELECT @TOTALCOUNT = COUNT(*)
FROM
#LOC_tKey WITH(NOLOCK)

--RETURN 1
SET ROWCOUNT 1000
SELECT #loc_tKey.RowID,
#loc_tKey.FolderID,
@TOTALCOUNT as TOTAL_COUNT,
WDtFOLDER.FLD_CATEGORY,
WDtFOLDER.FLD_SERIAL,
WDtFOLDER.FLD_SUB,
WDtFOLDER.FLD_FIRST,
WDtFOLDER.FLD_LAST,
(SELECT LOC_NAME
FROM WDtLocation
WHERE LOC_ID = WDtREQUEST.REQ_FROM_LOC) AS
REQ_FROM_LOC,
(SELECT LOC_NAME
FROM WDtLocation
WHERE LOC_ID = REQ_TO_LOC) AS
REQ_TO_LOC,
WDtREQUEST.REQ_DATE,
WDtREQUEST.REQ_USER

FROM
#loc_tKey JOIN WDtFOLDER WITH (NOLOCK)
ON #loc_tKey.FolderID = WDtFOLDER.FLD_ID,
WDtREQUEST

WHERE WDtREQUEST.REQ_ACTION = 'Request' AND
REQ_FOLDER_ID = FLD_ID AND
REQ_DATE = (SELECT MAX(REQ_DATE) FROM WDtRequest
WHERE REQ_DATE = @datReq_Date AND
REQ_FOLDER_ID = FLD_ID) AND
#loc_tKey.RowID >= @parm_StartRow
AND #loc_tKey.RowID <= @parm_EndRow

ORDER by WDtREQUEST.REQ_DATE

OPTION(FAST 1000)
END

Any help would be greatly appreciated.

Thanks,
Debbie

 

[clapag22]

You cannot REALLY eliminate the time, but you can default it to midnight. There a few ways to do this but I just do this:

convert(varchar(10), getdate(), 101)

You don't need to do another convert to get it back to datetime datatype as it will implicity convert.

This way when running queries you can always just say:

&quot;where datefield = '10/1/02'&quot;

 

[osjohnm]

Hi

This will return only the date portion of getdate:
select convert(varchar(10), getdate(), 101)
result = 10/10/2002
select convert(varchar(10), getdate(), 105)
result = 10-10-2002

You can change the '101' which is the style parameter for CONVERT depending of how you want to return the date part.

Under CONVERT in BOL the list of parameters and values are listed. Have a look there to decide how you wish to return the date. Depending on your server setup it might come out as yyyy/mm/dd or vice versa.

don't know if it was a pasting error but the insert SP right at the top was missing a bracket at the end.
change the getdate() part of the procedure to the above syntax:

CREATE PROCEDURE FolderRequest_Add
@intReq_Folder_ID int,
@datReq_Date datetime,
@strReq_User char(5),
@strReq_Action char(10),
@intReq_From_Loc int,
@intReq_To_Loc int
AS
INSERT into WDtRequest
(req_folder_id,
req_date,
req_user,
req_action,
req_from_loc,
req_to_loc
)
VALUES
(@intReq_Folder_ID,
convert(varchar(10), getdate(), 101),
@strReq_User,
'Request',
@intReq_From_Loc,
@intReq_To_Loc)

Hope that helps with your date question.

John

 

[debbie1212]

It works!!!!!!!!!

Thanks!!!!!!