April 16, 2012 at 9:42 am
Hello,
I've a table with a numeric and date columns. Assuming a,b are the column names, I'm writing a query which isn't returning any rows. Below is the query
select * from dbo.test group by b,a having b=CONVERT(datetime,getdate())
If condition isn't met then atleast null should be returned , not in this case. Any ideas? Thanks.
April 16, 2012 at 9:46 am
sqlbi.vvamsi (4/16/2012)
Hello,I've a table with a numeric and date columns. Assuming a,b are the column names, I'm writing a query which isn't returning any rows. Below is the query
select * from dbo.test group by b,a having b=CONVERT(datetime,getdate())
If condition isn't met then atleast null should be returned , not in this case. Any ideas? Thanks.
Two things, first I am assuming the b is defined as a datetime data type. Second, getdate() returns a datetime data type and doesn't need to be converted to a datetime data type.
Now, a question. Do you have a record in the table with the current date time, ie if I run the query at 2012-04-16 09:44:36.333, is there a record with that exact date and time? You are looking for an exact match to the current date and time in your query. An empty result set is just what I would expect in this case.
April 16, 2012 at 9:47 am
With out posting any table definitions or sample data it's hard to tell, but I assume that it is the getdate() causing you problems. It will return the date and time down to the milliseconds, which I doubt your table has any matches on. Try converting it to a date data type, rather than datetime if you only want records that occur on today's date.
April 16, 2012 at 9:50 am
sqlbi.vvamsi (4/16/2012)
Hello,I've a table with a numeric and date columns. Assuming a,b are the column names, I'm writing a query which isn't returning any rows. Below is the query
select * from dbo.test group by b,a having b=CONVERT(datetime,getdate())
If condition isn't met then atleast null should be returned , not in this case. Any ideas? Thanks.
1. Your query has a syntax problem! You are grouping results but not using any aggregate functions.
2. Your query dose stupid thing: converting result of GETDATE(0 to Datetime! Why?
It's GETDATE() returns datetime value itself
3. If you want the records returned when the "b" holds null value you can use:
select * from dbo.test
where b = getdate()
OR b IS NULL
Please note: GETDATE() returns exact time down to milliseconds!
April 16, 2012 at 9:52 am
Here b has "date" data type. Below is the query I tried. Still no result set.
select * from dbo.test group by b,a having b=CONVERT(date,getdate())
The above query returned when condition is met however on the flip side it is not returning any thing. I would want that query to return null if no match.
April 16, 2012 at 9:56 am
true, I want sum(a) in the query that is why I'm using a group by there. Below is the exact query I'm trying.
select sum(a) from dbo.test group by b having b=Convert(date,GETDATE())
April 16, 2012 at 9:58 am
sqlbi.vvamsi (4/16/2012)
true, I want sum(a) in the query that is why I'm using a group by there. Below is the exact query I'm trying.select sum(a) from dbo.test group by b having b=Convert(date,GETDATE())
And at the time you run this query, if there are no records with current date and time, you won't get a result set returned.
Run just the following: select getdate();
April 16, 2012 at 10:02 am
I want to capture the # of rows returned to an SSIS Variable in Execute SQL Task . As it is returning nothing I get an error in SSIS like expected single result set is not there.
April 16, 2012 at 10:08 am
Keep in mind that returning null is a row. When your query finds no rows like in your example it can't return null because it did not return any rows. Post some ddl, sample data and desired output. This is pretty simple but a lot harder when we don't what your data looks like.
_______________________________________________________________
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/
April 16, 2012 at 10:17 am
Ok. Here it is
CREATE TABLE [dbo].[test](
[a] [numeric] NULL,
[date] NULL
) ON [PRIMARY]
GO
a b
1 1981-08-14
2 1985-10-03
3 2012-04-13
select SUM(a) from test group by b having b=CONVERT(date,GETDATE())--This returns nothing
select SUM(a) from test where b=CONVERT(date,GETDATE())--This returns NULL
Pls correct me if I'm wrong, so when I use group by and having I'm essentially looking for group of data(result set) filtered on a condition. so it is returning nothing as it is not data but a result set. Right?
April 16, 2012 at 10:21 am
sqlbi.vvamsi (4/16/2012)
Ok. Here it isCREATE TABLE [dbo].[test](
[a] [numeric] NULL,
[date] NULL
) ON [PRIMARY]
GO
a b
1 1981-08-14
2 1985-10-03
3 2012-04-13
select SUM(a) from test group by b having b=CONVERT(date,GETDATE())--This returns nothing
select SUM(a) from test where b=CONVERT(date,GETDATE())--This returns NULL
Pls correct me if I'm wrong, so when I use group by and having I'm essentially looking for group of data(result set) filtered on a condition. so it is returning nothing as it is not data but a result set. Right?
The query with the having is looking for a group with the date time the query is run, you don't have one. The only dates in your data set are 1981-08-14, 1985-10-03, and 2012-04-13. In this query, change the test to b = cast('2012-04-13' as date).
April 16, 2012 at 10:22 am
sqlbi.vvamsi (4/16/2012)
Ok. Here it isCREATE TABLE [dbo].[test](
[a] [numeric] NULL,
[date] NULL
) ON [PRIMARY]
GO
a b
1 1981-08-14
2 1985-10-03
3 2012-04-13
select SUM(a) from test group by b having b=CONVERT(date,GETDATE())--This returns nothing
select SUM(a) from test where b=CONVERT(date,GETDATE())--This returns NULL
Pls correct me if I'm wrong, so when I use group by and having I'm essentially looking for group of data(result set) filtered on a condition. so it is returning nothing as it is not data but a result set. Right?
Yes. Your first query using group by and having would give you the sum of a for each value of b. However you then specified in the having to ONLY return those rows where the value of b = getdate().
In you second you said give the sum of a for all rows where b = getdate(). This returns null because there are no rows that meet your where clause.
As suggested before, it is HIGHLY unlikely you will ever find a row where b = getdate(). The most common ways of comparing with date is to either use convert to remove the timestamp portion or use relation checks (< or >).
_______________________________________________________________
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply