October 28, 2008 at 1:37 pm
I'm writing a query that is allowing the user from my web page to do a search with selecting parameters and a date parameter is one of them on the page. Not all params are required, actaully none of them are required but they're there if they need them.
So my question is, I have a DateTime column that has data stored in it as
2008-06-10 00:00:00.000, how can I create my query so that it only grabs the month and year and return that data?
so for example;
my page has
Start Date: 1/2008
End Date: 2/2008
I want to pass in 1/2008 as my start date and 2/2008 as my end date, how can I construct my query to work with that? Also, that start and end date are not required so no dates may be passed in to the query.
thanks
October 28, 2008 at 1:47 pm
Mike (10/28/2008)
I'm writing a query that is allowing the user from my web page to do a search with selecting parameters and a date parameter is one of them on the page. Not all params are required, actaully none of them are required but they're there if they need them.So my question is, I have a DateTime column that has data stored in it as
2008-06-10 00:00:00.000, how can I create my query so that it only grabs the month and year and return that data?
so for example;
my page has
Start Date: 1/2008
End Date: 2/2008
I want to pass in 1/2008 as my start date and 2/2008 as my end date, how can I construct my query to work with that? Also, that start and end date are not required so no dates may be passed in to the query.
thanks
i.e.
select convert(char(2),datepart(mm,getdate())) + '/' + convert(char(4),datepart(yyyy,getdate()))
Tommy
Follow @sqlscribeOctober 28, 2008 at 1:48 pm
Mike (10/28/2008)
I'm writing a query that is allowing the user from my web page to do a search with selecting parameters and a date parameter is one of them on the page. Not all params are required, actaully none of them are required but they're there if they need them.So my question is, I have a DateTime column that has data stored in it as
2008-06-10 00:00:00.000, how can I create my query so that it only grabs the month and year and return that data?
so for example;
my page has
Start Date: 1/2008
End Date: 2/2008
I want to pass in 1/2008 as my start date and 2/2008 as my end date, how can I construct my query to work with that? Also, that start and end date are not required so no dates may be passed in to the query.
thanks
With the start date and end date above, what would you be expected to be returned? Also, could the start date be 1/2008 and the end date 4/2008, and what would be the expected returned data set? And third, could one of the values be empty and how would that affect the result set?
October 28, 2008 at 2:59 pm
For passing dates into a procedure, I would recommend always passing the full date in YYYYMMDD format. The time will default to midnight.
If you can't do that - then, you can do something like:
DECLARE @myDate char(6);
SET @myDate = '200801';
SELECT CAST(@myDate + '01' AS datetime);
If you are using a stored procedure (recommended) - then you could setup the input parameters with a default. For example:
CREATE PROCEDURE dbo.MyProcedure
@startDate datetime = '20080101'
,@endDate datetime = '20080201'
AS
...
If the date parameters are not specified, they will be defaulted to those dates. However, that really is not very good because is hard-codes the dates. To make it a little more dynamic:
CREATE PROCEDURE dbo.MyProcedure
@startDate datetime = NULL
,@endDate datetime = NULL
AS
-- Default start date to beginning of last month
SET @startDate = dateadd(month, datediff(month, 0, coalesce(@startDate, getdate()) - 1, 0);
-- Default end date to beginning of this month
SET @endDate = dateadd(month, datediff(month, 0, coalesce(@endDate, getdate()), 0);
-- use the dates as
SELECT ...
FROM dbo.MyTable
WHERE DateColumn >= @startDate
AND DateColumn < @endDate;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 29, 2008 at 5:18 am
Lynn Pettis (10/28/2008)
Mike (10/28/2008)
I'm writing a query that is allowing the user from my web page to do a search with selecting parameters and a date parameter is one of them on the page. Not all params are required, actaully none of them are required but they're there if they need them.So my question is, I have a DateTime column that has data stored in it as
2008-06-10 00:00:00.000, how can I create my query so that it only grabs the month and year and return that data?
so for example;
my page has
Start Date: 1/2008
End Date: 2/2008
I want to pass in 1/2008 as my start date and 2/2008 as my end date, how can I construct my query to work with that? Also, that start and end date are not required so no dates may be passed in to the query.
thanks
With the start date and end date above, what would you be expected to be returned? Also, could the start date be 1/2008 and the end date 4/2008, and what would be the expected returned data set? And third, could one of the values be empty and how would that affect the result set?
With the start date and end date above, what would you be expected to be returned? Transactions that took place between the start and end date
Also, could the start date be 1/2008 and the end date 4/2008, and what would be the expected returned data set? Yes, again transactions between those dates
And third, could one of the values be empty and how would that affect the result set? Yes
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply