Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 770
  • Last Modified:

SQL DateAdd() Timestamp

Hello experts -

I am trying to pull all gifts dated the previous week using the attached code.  The results set has the correct dates but uses the current time as the time stamp.  Is there a way to set the timestamp as 00:00:00 so I can capture all the gifts?  Instead of 10/16/2011, 8:32:02 AM to 10/22/2011, 8:32:02 AM it would be 10/16/2011, 00:00:00 AM to 10/22/2011, 00:00:00 AM.

Thanks you.
Lisa

DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
 
--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+5), GetDate())

-- end declare variables for gifts in last week (Monday to Sunday)

select 
	CONVERT(VARCHAR, @StartOfPrevWeek-1,109) as FromDate,
	CONVERT(VARCHAR, @EndOfPrevWeek,109) as ToDate,

Open in new window

0
lcallah93
Asked:
lcallah93
  • 5
  • 3
1 Solution
 
Lee SavidgeCommented:
Like this you mean?

select
        CONVERT(VARCHAR(11), @StartOfPrevWeek-1,109) as FromDate,
        CONVERT(VARCHAR(11), @EndOfPrevWeek,109) as ToDate,
0
 
lcallah93Author Commented:
I think so - does that just take out the timestamp and give me all the gifts between the specified days?
0
 
mcs0506Commented:
You can use the following function to get only Date value

CREATE FUNCTION [dbo].[GetDateOnly] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

    RETURN CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)

END


regards

Dani
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Lee SavidgeCommented:
Well it depends on the whole query really. I didn't see a statement in there that grabs data from a table. Modifying this selet truncates the time stamp from the datetime. If you re-cast is back to a datetime you'll see it will not have a time element.
0
 
lcallah93Author Commented:
Sorry - all the necessary pieces of the query are below:


-- all tribute gifts for any honoree with a most recent gift date 
-- in the previous week (Sunday to Saturday)

use VCU
-- begin declare variables for gifts in last week (Monday to Sunday)

DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
 
--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+5), GetDate())

-- end declare variables for gifts in last week (Monday to Sunday)

select 
	CONVERT(VARCHAR(11), @StartOfPrevWeek-1,101) as FromDate,
	CONVERT(VARCHAR(11), @EndOfPrevWeek,101) as ToDate,
	donorgifts.giftprocdt as ProcessDate

from gifts_VIEW_donor_relations donorgifts

where donorgifts.giftprocdt between CONVERT(VARCHAR(11), @StartOfPrevWeek-1,7) -- gift is in last full week
		AND CONVERT(VARCHAR(11), @EndOfPrevWeek,7)

Open in new window

0
 
Lee SavidgeCommented:
Alter the last select to:

select
        cast(CONVERT(VARCHAR(10), @StartOfPrevWeek-1,101) as datetime) as FromDate,
        cast(CONVERT(VARCHAR(10), @EndOfPrevWeek,101)  as datetime) as ToDate,
        donorgifts.giftprocdt as ProcessDate

from gifts_VIEW_donor_relations donorgifts
0
 
Lee SavidgeCommented:
This changes the date format to mm/dd/yyyy and truncates it to a 10 character string. Then when you cast it back to datetime it has no time element so it sets the time as 00:00:00.000.

Watch the from and to dates though. You may need to alter the to date to cast(CONVERT(VARCHAR(10), @EndOfPrevWeek,101)  as datetime) + 1 otherwise you will search for data when the to date is midnight on the day in question and exclude anything on that actual date so sometimes it is best to add a day to make the query inclusive of both dates.

0
 
lcallah93Author Commented:
Is your last post (with the CAST command) just to show the time as 00:00:00 but the actual removal of the timestamp is done with your first post?  I ask because the query was slowed down considerably with the CAST command.  I am hoping I don't need it.
0
 
Lee SavidgeCommented:
It's best to do the selection with the correct format so it your fields you're comparing are datetime fields it makes sense to cast otherwise you rely on SQL's implicit casting which may (or may not) get it wrong.

The removal of the time portion is done when you convert to the mm/dd/yyyy string which is set to 10 characters in length. This method relies on SQL converting the date time to the format you requested which was 101, which does include the time portion, and then outputting that into a 10 character string. This effectively truncates the time off. When you cast it back to a datetime, the time portion is set by SQL to midnight, so the removal is done when you force the longer source string into a shorter target varchar. It's a neat trick I've used for years and considering all the ways there are of removing the time portion I have always found this way reliable, fast, easy to read and quick to code.

Lee
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now