October 9, 2012 at 12:26 pm
Hello,
I have a format for field1 example:20110228, I need to get all data from field1 that are within range 7 days from getdate and can't figour out how to do it.
Thank you
October 9, 2012 at 12:29 pm
Krasavita (10/9/2012)
Hello,I have a format for field1 example:20110228, I need to get all data from field1 that are within range 7 days from getdate and can't figour out how to do it.
Thank you
where field1 > DATEADD(d, -7, '20010228') and field1 <= '20010228'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2012 at 1:19 pm
No,but I need
this where field1 > DATEADD(d, -7, 'getdate()') and field1 <= 'getdate()
October 9, 2012 at 1:21 pm
Krasavita (10/9/2012)
No,but I needthis where field1 > DATEADD(d, -7, 'getdate()') and field1 <= 'getdate()
So you are all set then?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2012 at 1:29 pm
If I run WHERE (field1 > DATEADD(d, - 7, GETDATE()))
get error:arithmetic overflow
October 9, 2012 at 1:33 pm
Krasavita (10/9/2012)
If I run WHERE (field1 > DATEADD(d, - 7, GETDATE()))get error:arithmetic overflow
What datatype is field1?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2012 at 1:35 pm
int
October 9, 2012 at 1:39 pm
Krasavita (10/9/2012)
int
Reason #91029037403884 you should ALWAYS use the proper datatypes. Datetime should ALWAYS be stored in a datetime column. Otherwise you end up pulling out your hair over something as simple as this.
You will have to first cast this to varchar and then to datetime.
WHERE (cast(cast(field1 as varchar) as datetime) > DATEADD(d, - 7, GETDATE()))
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2012 at 1:50 pm
Thank you
October 9, 2012 at 1:57 pm
You're welcome. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2012 at 3:09 pm
Sean Lange (10/9/2012)
Krasavita (10/9/2012)
intReason #91029037403884 you should ALWAYS use the proper datatypes. Datetime should ALWAYS be stored in a datetime column. Otherwise you end up pulling out your hair over something as simple as this.
You will have to first cast this to varchar and then to datetime.
WHERE (cast(cast(field1 as varchar) as datetime) > DATEADD(d, - 7, GETDATE()))
Completely agree, but now I want to see the other 91029037403883 ones. 😛
-- Gianluca Sartori
October 9, 2012 at 3:11 pm
Gianluca Sartori (10/9/2012)
Sean Lange (10/9/2012)
Krasavita (10/9/2012)
intReason #91029037403884 you should ALWAYS use the proper datatypes. Datetime should ALWAYS be stored in a datetime column. Otherwise you end up pulling out your hair over something as simple as this.
You will have to first cast this to varchar and then to datetime.
WHERE (cast(cast(field1 as varchar) as datetime) > DATEADD(d, - 7, GETDATE()))
Completely agree, but now I want to see the other 91029037403883 ones. 😛
It's going to take me a bit to write them all down....please be patient...
So maybe there aren't that many, but I have helped at least that many people with datetime being stored in the wrong datatype.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2012 at 3:19 pm
Sean Lange (10/9/2012)
Gianluca Sartori (10/9/2012)
Sean Lange (10/9/2012)
Krasavita (10/9/2012)
intReason #91029037403884 you should ALWAYS use the proper datatypes. Datetime should ALWAYS be stored in a datetime column. Otherwise you end up pulling out your hair over something as simple as this.
You will have to first cast this to varchar and then to datetime.
WHERE (cast(cast(field1 as varchar) as datetime) > DATEADD(d, - 7, GETDATE()))
Completely agree, but now I want to see the other 91029037403883 ones. 😛
It's going to take me a bit to write them all down....please be patient...
So maybe there aren't that many, but I have helped at least that many people with datetime being stored in the wrong datatype.
Quite true. 😉
I usually point people to my article here on SSC: http://www.sqlservercentral.com/articles/T-SQL/88152/
Cheers
-- Gianluca Sartori
October 9, 2012 at 3:25 pm
Gianluca Sartori (10/9/2012)
Sean Lange (10/9/2012)
Gianluca Sartori (10/9/2012)
Sean Lange (10/9/2012)
Krasavita (10/9/2012)
intReason #91029037403884 you should ALWAYS use the proper datatypes. Datetime should ALWAYS be stored in a datetime column. Otherwise you end up pulling out your hair over something as simple as this.
You will have to first cast this to varchar and then to datetime.
WHERE (cast(cast(field1 as varchar) as datetime) > DATEADD(d, - 7, GETDATE()))
Completely agree, but now I want to see the other 91029037403883 ones. 😛
It's going to take me a bit to write them all down....please be patient...
So maybe there aren't that many, but I have helped at least that many people with datetime being stored in the wrong datatype.
Quite true. 😉
I usually point people to my article here on SSC: http://www.sqlservercentral.com/articles/T-SQL/88152/
Cheers
Agreed. However it turned out in this case that formatting was not the issue. It was improper datatypes. I don't think that formatting was ever actually a concern here...but your article is one that should be mentioned. If the OP does need to format it that is an excellent article to read about formatting dates.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply