February 15, 2016 at 3:50 am
Hi All,
I will try and explain as best I can.
I have the following parameters for my sp:
declare @CLUSTER_CODE as nvarchar(3)
declare@START_MONTH as nvarchar(2)
declare@START_YEAR as nvarchar(4)
declare@END_MONTH as nvarchar(2)
declare@END_YEAR as nvarchar(4)
declare@DIRECT_APPLY as bit
set @START_MONTH = '01'
set @START_YEAR = '2014'
set @END_MONTH = '02'
set @END_YEAR = '2016'
declare @START_DATE as datetime
declare @END_DATE as datetime
declare @PREVIOUS_START_DATE as datetime
declare @PREVIOUS_END_DATE as datetime
set @START_DATE = @START_YEAR + '-' + @START_MONTH + '-01 00:00:00'
set @END_DATE = @END_YEAR + '-' + @END_MONTH + '-01 00:00:00'
set @END_DATE = DateAdd(m,1,@END_DATE)
set @Previous_Start_Date = DATEADD( YY, -1, @START_DATE)
set @PREVIOUS_END_DATE = dateadd(YY, -1, @END_DATE)
so the previous_start_date and previous_end_date are not working and I think I have worked out why.
As we have to select parameters for the report, but I want the previous start and end dates to work themselves out from the start and end dates selected.
So I have assumed as it's only the year that needs to change, that I need to do the @start_year and @end_year parameters as minus 1 .
However how would I do this?
So effectively when it pulls the data it will have a column for how much it is this month in this year, and how much it was for 12 months ago.
I haven't included the whole query as it's this bit that I am struggling with.
February 15, 2016 at 4:00 am
It's better use proper date arithmetic than rely on character conversions that are less efficient and vulnerable to regional settings. Try something like this:
SET @START_DATE = DATEADD(MONTH,@START_MONTH-1,DATEADD(YEAR,@START_YEAR-1900,'19000101')
John
February 15, 2016 at 2:28 pm
From the data given your script gives this outcome:
(No column name)(No column name)
@START_DATE2014-01-01 00:00:00.000
@END_DATE2016-03-01 00:00:00.000
@Previous_Start_Date2013-01-01 00:00:00.000
@PREVIOUS_END_DATE2015-03-01 00:00:00.000
What does not look right for you here?
_____________
Code for TallyGenerator
February 16, 2016 at 10:55 am
Hi Sergiy,
I am getting a The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value error each time I try now, I was getting no data before.
That makes logical sense to me (as in what you wrote was right) just it's not working for me (I blame my tired brain).
February 16, 2016 at 12:48 pm
Kazmerelda (2/16/2016)
Hi Sergiy,I am getting a The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value error each time I try now, I was getting no data before.
That makes logical sense to me (as in what you wrote was right) just it's not working for me (I blame my tired brain).
That means that you somewhere have a comparison between nvarchar and datetime, or an assignment of nvarchar to datetime, and the nvarchar value does not represent a valid datetime value based on the conversion settings in effect.
If you double-click the error message in SSMS, you should normally be taken to the approximate location of the error. If you can identify which conversion it is, include a PRINT or SELECT of the nvarchar value to see what it looks like. In most cases, that will result in the familiar *facepalm* moment.
February 16, 2016 at 12:58 pm
Get rid of the dashes (-) in the date, they introduce errors (ambiguity).
Use format 'YYYYMMDD [hh:mm:ss]', which is always interpreted correctly, regardless of language/date settings.
declare @CLUSTER_CODE as nvarchar(3)
declare@START_MONTH as nvarchar(2)
declare@START_YEAR as nvarchar(4)
declare@END_MONTH as nvarchar(2)
declare@END_YEAR as nvarchar(4)
declare@DIRECT_APPLY as bit
set @START_MONTH = '01'
set @START_YEAR = '2014'
set @END_MONTH = '02'
set @END_YEAR = '2016'
declare @START_DATE as datetime
declare @END_DATE as datetime
declare @PREVIOUS_START_DATE as datetime
declare @PREVIOUS_END_DATE as datetime
set @START_DATE = @START_YEAR + RIGHT('0' + @START_MONTH, 2) + '01 00:00:00'
set @END_DATE = @END_YEAR + RIGHT('0' + @END_MONTH, 2) + '01 00:00:00'
set @END_DATE = DateAdd(m,1,@END_DATE)
set @Previous_Start_Date = DATEADD( YY, -1, @START_DATE)
set @PREVIOUS_END_DATE = dateadd(YY, -1, @END_DATE)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 16, 2016 at 1:10 pm
ScottPletcher (2/16/2016)
Get rid of the dashes (-) in the date, they introduce errors (ambiguity).Use format 'YYYYMMDD [hh:mm:ss]', which is always interpreted correctly, regardless of language/date settings.
Sorry, but that is not entirely true. For datetime, the only guaranteed unambiguous formats are:
* yyyymmdd for date only
* yyyy-mm-ddThh:mm:ss[.ttt] for date and time (where [.ttt] represents the optional thousands of seconds).
For the newer data types (date, datetime2, ...), yyyy-mm-dd is also okay. But for datetime, this is not safe.
February 16, 2016 at 1:26 pm
Hugo Kornelis (2/16/2016)
ScottPletcher (2/16/2016)
Get rid of the dashes (-) in the date, they introduce errors (ambiguity).Use format 'YYYYMMDD [hh:mm:ss]', which is always interpreted correctly, regardless of language/date settings.
Sorry, but that is not entirely true. For datetime, the only guaranteed unambiguous formats are:
* yyyymmdd for date only
* yyyy-mm-ddThh:mm:ss[.ttt] for date and time (where [.ttt] represents the optional thousands of seconds).
For the newer data types (date, datetime2, ...), yyyy-mm-dd is also okay. But for datetime, this is not safe.
:unsure: YYYYMMDD followed by 24-hour time in format ' HH:MM:SS.sssssss' is unambiguous as well. How/under what conditions/settings could it possibly be misinterpreted?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 16, 2016 at 1:35 pm
ScottPletcher (2/16/2016)
Hugo Kornelis (2/16/2016)
ScottPletcher (2/16/2016)
Get rid of the dashes (-) in the date, they introduce errors (ambiguity).Use format 'YYYYMMDD [hh:mm:ss]', which is always interpreted correctly, regardless of language/date settings.
Sorry, but that is not entirely true. For datetime, the only guaranteed unambiguous formats are:
* yyyymmdd for date only
* yyyy-mm-ddThh:mm:ss[.ttt] for date and time (where [.ttt] represents the optional thousands of seconds).
For the newer data types (date, datetime2, ...), yyyy-mm-dd is also okay. But for datetime, this is not safe.
:unsure: YYYYMMDD followed by 24-hour time in format ' HH:MM:SS.sssssss' is unambiguous as well. How/under what conditions/settings could it possibly be misinterpreted?
I double checked the ultimate guide on datetime datatypes (http://www.karaszi.com/sqlserver/info_datetime.asp), and it turns out that you are right. I have this emplanted deepp into my memory, and now I have to wipe all that.
Thanks for the correction!
February 16, 2016 at 3:20 pm
ScottPletcher (2/16/2016)
Get rid of the dashes (-) in the date, they introduce errors (ambiguity).Use format 'YYYYMMDD [hh:mm:ss]', which is always interpreted correctly, regardless of language/date settings.
+1.
But better - go with the advice from John Mitchell.
Get rid of the bad habit of producing dates from varchars.
_____________
Code for TallyGenerator
February 17, 2016 at 8:11 am
My apologies John Mitchell, I didn't see your reply!
Thank you all for the advice got it working using a combination of your advice. Really appreciate it, I need to revisit my datetime formats I feel.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply