How should I find the dates for 3 days past and 5 days future. Such as TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
Hi Jim,
You would have to use DATEADD function in your SQL sentence.
SELECT current, DATEADD( day, -3, current ) AS past, DATEADD( day, 5, current ) AS future
FROM t_table;
If you want use to today. You would replace 'current' column by GETDATE() function.
Good Coding!
Javier Luna
http://guydotnetxmlwebservices.blogspot.com/
|||
Also, to get rid of time you can do something like this:
select cast(convert(varchar(10),getdate(),112) as datetime)
|||
JIM.H. wrote:
How should I find the dates for 3 days past and 5 days future. Such as TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
SELECT convert(varchar(15), DATEADD( day, -3, GETDATE()), 101) AS [3 Days Ago], convert(varchar(15), DATEADD( day, 5, GETDATE()), 101) AS [5 days from now]
Adamus
|||Nope,
Anamus, this is my code :)
Good Coding!
Javier Luna
http://guydotnetxmlwebservices.blogspot.com/
|||
This is likely to be a bit more efficient, since it doesn't
require converting from datetime to varchar:
SELECT
dateadd(day,datediff(day,0,getdate())-3,0) AS [3 Days Ago],
dateadd(day,datediff(day,0,getdate())+5,0) AS [5 Days from now]
Steve Kass
Drew University
www.stevekass.com
JIM.H.@.discussions.microsoft.com wrote:
> How should I find the dates for 3 days past and 5 days future. Such as
> TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
>
>
|||This incorporates the time. The requestor does not want the time.This is likely to be a bit more efficient, since it doesn't
require converting from datetime to varchar:
SELECT
dateadd(day,datediff(day,0,getdate())-3,0) AS [3 Days Ago],
dateadd(day,datediff(day,0,getdate())+5,0) AS [5 Days from now]
Steve Kass
Drew University
www.stevekass.com
JIM.H.@.discussions.microsoft.com wrote:
> How should I find the dates for 3 days past and 5 days future. Such as
> TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
>
>
Adamus
|||Adamus Turner@.discussions.microsoft.com wrote:
> [quote user="NNTP User"]
>
> This is likely to be a bit more efficient, since it doesn't
> require converting from datetime to varchar:
>
>
> SELECT
> dateadd(day,datediff(day,0,getdate())-3,0) AS [3 Days Ago],
> dateadd(day,datediff(day,0,getdate())+5,0) AS [5 Days from now]
>
> Steve Kass
> Drew University
> www.stevekass.com
>
> JIM.H.@.discussions.microsoft.com wrote:
>
>
>>How should I find the dates for 3 days past and 5 days future. Such as
>>TodayDate-3 and TodayDate+5 base in the date only, discarding the
>
> time?
>
>>
>
>
> [/quote]This incorporates the time. The requestor does not want the
> time.
>
> Adamus
>
>
This does not incorporate the time. Did you test it?
SK
|||Yes...the time is 00:00:00:0000
Bad format
Did you test it?
Adamus
|||Yes, I tested it.The query I posted was
SELECT
dateadd(day,datediff(day,0,getdate())-3,0) AS [3 Days Ago],
dateadd(day,datediff(day,0,getdate())+5,0) AS [5 Days from now]
The result in SQL Server (Query Analyzer, standard installation) is
3 Days Ago 5 Days from now
- -
2006-09-01 00:00:00.000 2006-09-09 00:00:00.000
Perhaps you think that the OP did not want the 00:00:00.000 to appear. Perhaps. Jim wanted to "find the dates," "discarding the time." There is no DATE data type in SQL Server, so I thought the best solution was to find the dates with midnight as the time. That's commonly referred to as a datetime without a time part. I don't think it's a good idea to select a string. The result set of the query you suggested, for example, would be interpreted as January 9 and September 9 in some cultures, and as September 1 and September 9 in others. My results would be presented and interpreted correctly regardless of culture.
SK|||Are you also NNTPUser?|||Thanks Steve. I am using this.|||
The comment on culture is incorrect. Converting the date to a string will not change the compiler interpretation of midnight. In fact, it will set the time as midnight. At no time, and under no circumstance will the date be incorrect.Yes, I tested it.
The query I posted was
SELECT
dateadd(day,datediff(day,0,getdate())-3,0) AS [3 Days Ago],
dateadd(day,datediff(day,0,getdate())+5,0) AS [5 Days from now]
The result in SQL Server (Query Analyzer, standard installation) is
3 Days Ago 5 Days from now
- -
2006-09-01 00:00:00.000 2006-09-09 00:00:00.000
Perhaps you think that the OP did not want the 00:00:00.000 to appear. Perhaps. Jim wanted to "find the dates," "discarding the time." There is no DATE data type in SQL Server, so I thought the best solution was to find the dates with midnight as the time. That's commonly referred to as a datetime without a time part. I don't think it's a good idea to select a string. The result set of the query you suggested, for example, would be interpreted as January 9 and September 9 in some cultures, and as September 1 and September 9 in others. My results would be presented and interpreted correctly regardless of culture.
SK
As a side note: It is very poor programming practice to misuse primitive functions such as datediff() as some form of clever workaround to help justify an inability to correctly resolve an issue.
In good programming ethics and to avoid maintenace confusion, this elementary trickery should be avoided at all costs.
Adamus
|||Perhaps English is your second or third language:Yes, I tested it. The query I posted was
SELECT
dateadd(day,datediff(day,0,getdate())-3,0) AS [3 Days Ago],
dateadd(day,datediff(day,0,getdate())+5,0) AS [5 Days from now]The result in SQL Server (Query Analyzer, standard installation) is
3 Days Ago 5 Days from now
- -
2006-09-01 00:00:00.000 2006-09-09 00:00:00.000Perhaps you think that the OP did not want the 00:00:00.000 to appear. Perhaps. Jim wanted to "find the dates," "discarding the time." There is no DATE data type in SQL Server, so I thought the best solution was to find the dates with midnight as the time. That's commonly referred to as a datetime without a time part. I don't think it's a good idea to select a string. The result set of the query you suggested, for example, would be interpreted as January 9 and September 9 in some cultures, and as September 1 and September 9 in others. My results would be presented and interpreted correctly regardless of culture.
SK
"How should I find the dates for 3 days past and 5 days future. Such as TodayDate-3 and TodayDate+5 base in the date only, discarding the time?"
|||Adamus Turner@.discussions.microsoft.com wrote:
> This post has been edited either by the author or a moderator in the
> Microsoft Forums: http://forums.microsoft.com
>
> [quote user="Steve Kass"]Yes, I tested it.
>
> The query I posted was
>
> SELECT
> dateadd(day,datediff(day,0,getdate())-3,0) AS [3 Days Ago],
> dateadd(day,datediff(day,0,getdate())+5,0) AS [5 Days from now]
>
> The result in SQL Server (Query Analyzer, standard installation) is
>
> 3 Days Ago 5 Days from now
> - -
> 2006-09-01 00:00:00.000 2006-09-09 00:00:00.000
>
> Perhaps you think that the OP did not want the 00:00:00.000 to appear.
> Perhaps. Jim wanted to "find the dates," "discarding the time." There is
> no DATE data type in SQL Server, so I thought the best solution was to
> find the dates with midnight as the time. That's commonly referred to as
> a datetime without a time part. I don't think it's a good idea to select
> a string. The result set of the query you suggested, for example, would
> be interpreted as January 9 and September 9 in some cultures, and as
> September 1 and September 9 in others. My results would be presented and
> interpreted correctly regardless of culture.
>
> SK
> [/quote]The comment on culture is incorrect. Converting the date to a
> string will not change the compiler interpretation of midnight. In fact,
> it will set the time as midnight. At no time, and under no circumstance
> will the date be incorrect.
I never said the time portion might be interpreted incorrectly.
But you are simply wrong about the date. The string results of your
code will represent wrong dates depending on the culture. Here is a
repro using the code you posted to show that:
set language French
go
create table AdamusDates (
[3 Days Ago] datetime,
[5 days from now] datetime
)
insert into AdamusDates
SELECT
convert(varchar(15), DATEADD( day, -3, GETDATE()), 101) AS [3 Days Ago],
convert(varchar(15), DATEADD( day, 5, GETDATE()), 101) AS [5 days from now]
set language English
insert into AdamusDates
SELECT
convert(varchar(15), DATEADD( day, -3, GETDATE()), 101) AS [3 Days Ago],
convert(varchar(15), DATEADD( day, 5, GETDATE()), 101) AS [5 days from now]
go
select * from AdamusDates
go
drop table AdamusDates
Results:
3 Days Ago 5 days from now
-- --
2006-02-09 00:00:00.000 2006-10-09 00:00:00.000
2006-09-02 00:00:00.000 2006-09-10 00:00:00.000
>
> As a side note: It is very poor programming practice to misuse primitive
> functions such as dateadd() as some form of clever workaround to help
> justify an inability to correctly resolve an issue. DateDiff()?
Dateadd() and Datediff() are well-documented and entirely appropriate
to a question about dates. Apparently you would prefer to maintain your
inability to correctly resolve the issue, as some form of obstinate
resistance to the use of language elements that would solve the problem.
>
> In good programming ethics and to avoid maintenace confusion, this
> elementary trickery should be avoided at all costs.
It's not trickery. It's a simple application of documented behavior
of T-SQL. And it works in the US, in France, and elsewhere.
SK
>
> Adamus
>
>
JIM.H. wrote: