June 20, 2012 at 8:02 am
Here is the query I am working with.
select *
from table where cast (postdate AS int(128)) between ({fn Curdate()}) and ({fn Curdate()}) + ({fn minute(1439)})
I originally had this
select *
from table where postdate between ({fn Curdate()}) and ({fn Curdate()}) + ({fn minute(1439)})
But recieved this error - "Conversion failed when converting the varchar value '2012-06-20' to data type int."
Basically I want to run the query where postdate shown is any time within the start of the current date and the end of the current day.
Any suggestions??
June 20, 2012 at 8:35 am
dustinprevatt (6/20/2012)
Here is the query I am working with.select *
from table where cast (postdate AS int(128)) between ({fn Curdate()}) and ({fn Curdate()}) + ({fn minute(1439)})
I originally had this
select *
from table where postdate between ({fn Curdate()}) and ({fn Curdate()}) + ({fn minute(1439)})
But recieved this error - "Conversion failed when converting the varchar value '2012-06-20' to data type int."
Basically I want to run the query where postdate shown is any time within the start of the current date and the end of the current day.
Any suggestions??
I have a couple. Drop those scalar date functions you have like a hot potato. They are going to do nothing but cause you pain.
Take a look at Lynn's blog post. He has all the date logic you could ever need. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
If you still need some help after that please read the first link in my signature about best practices when posting questions.
_______________________________________________________________
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/
June 20, 2012 at 8:56 am
Is getdate the same as curdate?
If so how would i use the example in the link to show the beggining of the current day thru the end of the current day?
Sorry i'm still a bit confused.
:unsure:
June 20, 2012 at 8:59 am
dustinprevatt (6/20/2012)
Here is the query I am working with.select *
from table where cast (postdate AS int(128)) between ({fn Curdate()}) and ({fn Curdate()}) + ({fn minute(1439)})
I originally had this
select *
from table where postdate between ({fn Curdate()}) and ({fn Curdate()}) + ({fn minute(1439)})
But recieved this error - "Conversion failed when converting the varchar value '2012-06-20' to data type int."
Basically I want to run the query where postdate shown is any time within the start of the current date and the end of the current day.
Any suggestions??
Here is what you are trying to get.
select
* -- should list all columns to be returned, not using *
from
dbo.table t -- your actual table here
where
t.postdate >= dateadd(dd, datediff(dd, 0, getdate()), 0) and
t.postdate < dateadd(dd, datediff(dd, 0, getdate()) + 1, 0);
June 20, 2012 at 9:01 am
dustinprevatt (6/20/2012)
Is getdate the same as curdate?If so how would i use the example in the link to show the beggining of the current day thru the end of the current day?
Sorry i'm still a bit confused.
:unsure:
Well I have no idea what those function do. They are UDFs in your system. Look above at Lynn's post. He gave a great starting point.
_______________________________________________________________
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/
June 20, 2012 at 9:05 am
dustinprevatt (6/20/2012)
Is getdate the same as curdate?If so how would i use the example in the link to show the beggining of the current day thru the end of the current day?
Sorry i'm still a bit confused.
:unsure:
This, {fn Curdate()}, is an ODBC date function. You really shouldn't be using them. I would recommend you start here and begin learning about the functions available in T-SQL, http://msdn.microsoft.com/en-us/library/ms174318.aspx.
June 20, 2012 at 9:14 am
It worked!
Could anyone go into detail about what this actually means ?
postdate>= dateadd(dd, datediff(dd, 0, getdate()), 0) and postdate < dateadd(dd, datediff(dd, 0, getdate()) + 1, 0)
I'm glad this works! Thank you All!
It would just be nice to better understand it.
June 20, 2012 at 9:21 am
dustinprevatt (6/20/2012)
It worked!Could anyone go into detail about what this actually means ?
postdate>= dateadd(dd, datediff(dd, 0, getdate()), 0) and postdate < dateadd(dd, datediff(dd, 0, getdate()) + 1, 0)
I'm glad this works! Thank you All!
It would just be nice to better understand it.
select
cast(cast('19000101' as datetime) as int), -- returns 0
cast(0 as datetime) -- returns 1900-01-01 00:00:00.000
This:
dateadd(dd, datediff(dd, 0, getdate()), 0)
Is equivalent to this:
dateadd(dd, datediff(dd, cast('19000101' as datetime), getdate()), cast('19000101' as datetime))
1900-01-01 is the 0 date.
Best thing to do is play with the functions and figure out what the are doing and why.
June 20, 2012 at 9:22 am
dustinprevatt (6/20/2012)
It worked!Could anyone go into detail about what this actually means ?
postdate>= dateadd(dd, datediff(dd, 0, getdate()), 0) and postdate < dateadd(dd, datediff(dd, 0, getdate()) + 1, 0)
I'm glad this works! Thank you All!
It would just be nice to better understand it.
Well if you take each of the conditions and make that a select statement it will probably help a little bit.
select dateadd(dd, datediff(dd, 0, getdate()), 0)
select dateadd(dd, datediff(dd, 0, getdate()) + 1, 0)
The basic piece of understanding here is a 0 date.
select CAST(0 as datetime)
This will equate to 1/1/1900. Now we have a base from which to perform calculations. So the first one adds the number of days since 1/1/1900 to get the beginning of the current day. In effect this sets the timestamp to all 0's in a really fast way.
The second one just adds 1 day to the same logic to get tomorrow's date with a 0 timestamp.
_______________________________________________________________
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/
June 20, 2012 at 9:23 am
And now you have two explanations to the same thing. 😀 There I go trying to explain other people's work again...
_______________________________________________________________
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply