March 14, 2016 at 7:35 am
I have date and time stored in the following format: mm-dd-yyyy hh:mm:ss
I only need the date part, and want to use a data parameter where I specify only one date ie 14/03/2016
So I have used
convert(varchar,ScheduledActivity.ScheduledDateTime,103)
This gives me what I want in the output, but if I search for ScheduledActivity.ScheduledDateTime = 13/04/2016 I get the following error, or no rows returned at all. 'The conversion of a varchar datatype to a datetime datatype resulted in an out of range value'
What should I be doing? It works of I use a between and 2 dates but I only want the user to enter one date into the report parameter.
Thanks
March 14, 2016 at 7:51 am
What data type is the ScheduledDateTime column in your database?
John
March 14, 2016 at 8:00 am
Don't convert the column's data types, especially from date/time data types to strings.
Convert the parameters to the column's data type.
It should look like this:
ScheduledActivity.ScheduledDateTime = CONVERT(datetime, '13/04/2016', 103)
If you don't want to use CONVERT, then use ISO date formats
YYYYMMDD for dates
YYYY-MM-DD hh:mi:ss.ms for date and time
March 14, 2016 at 8:03 am
I know you may not have control over this, but datetimes should be stored using a datetime data type, it makes things much easier. If your column is a datetime data type then you don't need to convert it to a specific format which makes it a string.
The error you are getting is because the default date format setting is based on your LANGUAGE setting. Here's an example:
SELECT
@@LANGUAGE AS LanguageInUse;
DECLARE
@DateString VARCHAR(20) = '14/03/2016',
@Date DATE;
/* Fails */
SET @Date = @DateString;
SELECT @Date
GO
SET LANGUAGE 'British'
SELECT
@@LANGUAGE AS LanguageInUse;
DECLARE
@DateString VARCHAR(20) = '14/03/2016',
@Date DATE;
/* Works */
SET @Date = @DateString;
SELECT @Date
GO
SET LANGUAGE 'us_english'
If you always send in your dates and times using the ISO format (yyyymmdd or yyyy-mm-dd) the language setting doesn't matter.
In your example I would do this, if the date and time is stored as a string and assuming you pass in the date string in yyyy-mm-dd:
DECLARE @Date DATE = '2016-03-14'
SELECT
*
FROM
dbo.ScheduledActivity
WHERE
CONVERT(DATETIME, ScheduledActivity.ScheduledDateTime) = @Date;
If you have to use dmy format then this would work:
SET DATEFORMAT 'dmy';
DECLARE @Date DATE = '14/03/2016';
SELECT
*
FROM
dbo.ScheduledActivity
WHERE
CONVERT(DATETIME, ScheduledActivity.ScheduledDateTime) = @Date;
But you should try to avoid using set options in queries because certain set options force a recompile of the plan every time.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 14, 2016 at 8:04 am
Thanks, but using that returns no records, and I know that it should return something. I have written in the WHERE:
ScheduledActivity.ScheduledStartTime = CONVERT(datetime, '14/03/2016',103)
The datetime field in the output is like this:
2016-03-14 14:30:00.0000
March 14, 2016 at 8:08 am
I don't want to use a declare function as I am using the sql query to feed a report where the report parameter is a single date entered by the person running the report, and this date will always change.
Is there a way I can select a date from a date time field? I have used the convert 103 function but this still doesn't seem to return any rows.
March 14, 2016 at 8:14 am
Set the parameter as a date type, then change the condition to this:
WHERE ScheduledActivity.ScheduledStartTime >= @Date
AND ScheduledActivity.ScheduledStartTime < DATEADD(DD,1,@Date)
Don't use strings if they're not needed.
March 14, 2016 at 8:19 am
What reporting platform are you using? You should parameterize the query and I'm sure whatever reporting platform you are using supports parameters.
The reason your query using ScheduledActivity.ScheduledStartTime = CONVERT(datetime, '14/03/2016',103) isn't returning data is because your column has the time and your "parameter" does not have the time and converting '14/03/2016' using format 103 returns '14/03/2016', you want format 110 to get mm-dd-yyyy.
For proper results I still believe your best case is to convert the column do a datetime and then do a datetime parameter for the comparison. Just realize that doing this:
1. Eliminates any chance for an index seek using the ScheduledStartTime column
2. That using "=" still requires you to match the time, down to the smallest increment stored.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 14, 2016 at 8:21 am
That does work, thanks. But it means I cannot use it the way I need to. It would be fine is I was running the sql manually but I am going to embed it into a reporting package which asks for a parameter in this type of form:
ScheduledActivity.ScheduledStartTime = :StartDate
Then I use the name :StartDate for an input parameter. With the declare function, I cant select a field/date parameter in the way I need to.
March 14, 2016 at 8:23 am
ScheduledActivity.ScheduledStartTime = CONVERT(datetime, '14/03/2016',110) gives me no values returned and that same out of range error
March 14, 2016 at 8:33 am
macdca (3/14/2016)
ScheduledActivity.ScheduledStartTime = CONVERT(datetime, '14/03/2016',110) gives me no values returned and that same out of range error
AS long as your Language setting is one where the default date format is mdy then converting a string that is dmy will fail. You either need to change the language setting of your session using SET LANGUAGE or the date format of your session using SET DATEFORMAT 'dmy', as in the example I included in my first post.
If your data has time values in it then passing in a value that doesn't include the time will likely never return rows. In order to pass in a data value without the time and return rows that have the time you need to use a data range as Luis has already posted or you need to convert the column to a date without time (no index seeks possible).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 14, 2016 at 8:34 am
"or you need to convert the column to a date without time (no index seeks possible)." - how do I do that?
March 14, 2016 at 8:41 am
macdca (3/14/2016)
"or you need to convert the column to a date without time (no index seeks possible)." - how do I do that?
CONVERT(DATE, ScheduledActivity.ScheduledDateTime)
As the number of rows in the table grows your performance will get worse pretty quickly.
Doing this essentially does the same thing as the range query Luis supplied and at least with the range query you have a chance at index seeks. I've got to think that your reporting platform (which is?) has a way to set 2 parameters and for you to manipulate the parameter values so the user only has to supply one.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 14, 2016 at 8:44 am
That works, thanks!:-)
March 14, 2016 at 8:58 am
Jack Corbett (3/14/2016)
macdca (3/14/2016)
"or you need to convert the column to a date without time (no index seeks possible)." - how do I do that?
CONVERT(DATE, ScheduledActivity.ScheduledDateTime)As the number of rows in the table grows your performance will get worse pretty quickly.
Doing this essentially does the same thing as the range query Luis supplied and at least with the range query you have a chance at index seeks. I've got to think that your reporting platform (which is?) has a way to set 2 parameters and for you to manipulate the parameter values so the user only has to supply one.
Converting a datetime column to date, allows index seeks. It's one of those cases that it would happen, so I don't recommend it on a first basis or people will believe it's ok to convert the data types of columns.
On a different note, what a bad reporting tool that won't allow to use parameters in different ways.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply