| Author |
Topic |
|
matta0990
Starting Member
44 Posts |
 Posted - 2011-09-14 : 11:46:27
|
| I want to convert this parameter to a datePeriod_Id >=@Start_Date and Period_Id <=@End_DatesThe parameter is passed through as a string '01 sept 11' i need it to convert it to '20110901'any help? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-14 : 11:50:04
|
| thats ok. so far as Period_ID is of type datetime and your language settings is english it will work fine------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-09-14 : 13:09:52
|
| If it is really SEPT, then you'll have to do a custom conversion. Even if SQL could implicitly convert that string to a date/datetime, implicit conversions are a bad idea. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-09-14 : 17:34:42
|
quote:
Originally posted by X002548
quote:
Originally posted by Lamprey
If it is really SEPT, then you'll have to do a custom conversion. Even if SQL could implicitly convert that string to a date/datetime, implicit conversions are a bad idea.
Bad Practice...there are no bad ideas I was told once... Brett
Touche!  |
|
|
|
matta0990
Starting Member
44 Posts |
Posted - 2011-09-15 : 03:46:08
|
| No, Period_Id is nvarchar.... I did not create the table and also not allowed to edit it as its part of a massive database. When i create my report it requires the parameter Period_Id, the user selects the relivent parameter from a drop down menu based on a stored proc from the database. The stored proc returns the last 50 Period_Id's and the user selects one which is then passes through the parameter. This should run my report, BUT as the stored Proc returns '01 SEP 11' my report needs to read it as '20110901'... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-15 : 04:03:04
|
| then you've to use CONVERT() function to convert it to datetimelikeSELECT convert(datetime,Period_ID,106)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
matta0990
Starting Member
44 Posts |
Posted - 2011-09-15 : 05:04:32
|
| When i try and convert this to datetime i get a error... Conversion failed when converting date and/or time from character string.? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-15 : 05:19:40
|
| are you having value like12 Sep 2011ot12 Sept 2011ie. is it always DD MMM YYYY format?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
matta0990
Starting Member
44 Posts |
Posted - 2011-09-15 : 05:26:41
|
| Its '12 sep 2011' that’s what the stored proc returns.So that is what is passed into my query as the parameter as '@start_date' for Period_Id... but the Period_Id needs to read it as '20110912' so i need a way of converting it in my query so that when '12 sep 2011' is selected by the users, the query converts it to '20110912' to pass into the parameter.... |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-09-15 : 12:00:08
|
There are a couple options:1. The first best option is to fix you database. (seriously)2. You can convert the "DD MMM YYYY" string to a date, then convert that to a string to compare against the other string:WHERE Period_Id >= CONVERT(NVARCHAR(8), CONVERT(DATE, @start_date, 106), 112) 3. You can convert the "DD MMM YYYY" sring to a date and convert teh Period_ID to a date and compare. But, this method will not be able to take advantage of any indexs, if any exist: WHERE CAST(Period_Id AS DATE) >= CONVERT(DATE, @start_date, 106) |
|
|
|
|
EASY! Convert to date