Converting Date Data type in stored procedure

HI Experts...

I am using SQL SERVER 2005 standard edition

I have encountered a problem regarding converting date data type in stored procedure

As i was having problem taking date as input parameter in my stored procedure, so, then I changed to varchar (16) i.e.

CREATE PROCEDURE sp_CalendarCreate
@.StDate VARCHAR(16) ,
@.EDate VARCHAR(16),

then I am converting varchar to date with following code

DECLARE @.STARTDATE DATETIME
DECLARE @.ENDDATE DATETIME

SELECT @.STARTDATE = CAST(@.STDATE AS DATETIME)
SELECT @.ENDDATE = CAST(@.EDATE AS DATETIME)

When I try to execute the procedure with following code

execute sp_CalendarCreate @.stdate='12-1-06',@.edate='20-1-06'

but it gives me following error

Msg 242, Level 16, State 3, Procedure sp_CalendarCreate, Line 45
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Can any one tell me the solution of that problem (at ur earliest)

regards,

Anas

execute sp_CalendarCreate @.stdate='1-12-06',@.edate='1-20-06'

|||

Thanx for your reply....

yes i had sorted that out b4 u replied me... i.e. sql server uses date format mmddyy or yyyyddmm (american format)

and i was using UK/european standards

btw thanx very much for support

regards,

Anas

|||It is best to use ISO unseparated date format (YYYMMDD) or ISO 8601 (YYYY-MM-DDThh:mm:ss.nnn) datetime format for specifying values. This way you don't have to worry about DATEFORMAT or language settings of the server. Also, using varchar and converting to datetime is not a good and clean approach. Use the correct data type for the values so you can get the best benefit in terms of type checking, domain validations etc. Additionally, with your approach you will get into performance problems because parameter sniffing of the variables used in the SELECT statement will not work due to use of local variables and not parameters. See the whitepaper on compilation, caching in MSDN for more details.

convert question please help

hi experts,
i have coordinates stored in a database in form of 452031 5222500 they are float and i want to change them to 4.52031 and 52.22500 so always take the first number then put the dot then the rest and with the second one take the first 2 numbers put the dot and then the rest, i realized that if i did it with the comma instead of the dot and then convert these 2 in string then it will change the comma to the dot automatically, the result can be also a string.
any help will be very appreciated.

thanksHey mrjoka,

Try this. Hope its what you were lookign for.

Cheers,
Justin

-----------------------
-- Declare variables
declare @.number_1 int
declare @.number_1_len int
declare @.number_2 int
declare @.number_2_len int

-- initialise variables
set @.number_1 = 452031
-- get length of number_1
set @.number_1_len = (select len(@.number_1))
set @.number_2 = 5222500
-- get length of number_2
set @.number_2_len = (select len(@.number_2))

-- Format result
select @.number_1 as old_format, CAST(left(@.number_1, 1) as varchar) + '.' + CAST(right(@.number_1, @.number_1_len-1) as varchar) as new_format
select @.number_2 as old_format, CAST(left(@.number_2, 2) as varchar) + '.' + CAST(right(@.number_2, @.number_1_len-2) as varchar) as new_format|||It's Thanksgiving, so I'm feeling especially NZDF today! Enjoy!-- ptp 20071122 see http://www.dbforums.com/showthread.php?t=1624685

DECLARE @.c VARCHAR(50)

SET @.c = '452031 5222500'

SELECT
Stuff(Left(@.c, CharIndex(' ', @.c)), 2, 0, '.')
, LTrim(Stuff(SubString(@.c, PatIndex('% [0-9]%', @.c), 50), 4, 0, '.'))-PatP|||thanks for thw quick reply, but i have a table called location where i have 1652 records i want just column X and Y to be converted.
any idea how to do that?

thanks|||hi experts,

Oh, stop it!

Regards,|||you stop it

never mind i found it myself with some string manipulation.

thanks for the help|||Oh geez, I must remember to insert those humour tags next time, that is, if I can remember where I've left them.

Regards,|||thanks for thw quick reply, but i have a table called location where i have 1652 records i want just column X and Y to be converted.
any idea how to do that?

thanksNo, but if you can post the CREATE TABLE statement, and three or more rows of data, then I could probably "wing it" for you.

The general idea would be to write an UPDATE statement that used your source column name instead of the @.c in my example to set the values for the two new columns using the code that I posted. Once you post the schema and a few rows of data, it should only take a minute to cook up and test.

-PatP|||I'm guessing that he means 452031 5222500 are the two values from the columns X and Y, and he wants a "dot" inserted after the first digit of X and after the second digit of Y. I think.
That Stuff function will work like this:

SELECT STUFF(X, 2, 0, '.') AS X, STUFF(Y, 3, 0, '.') AS Y
FROM location

But if you are going to Update that back into columns of type float, you'll probably need a cast.

Is that what you mean mrjoka?|||452031 5222500 they are float and i want to change them to 4.52031 and 52.22500 update daTable
set x = x / 10000
, y = y / 10000|||set x = x / 10000
, y = y / 10000

That's assuming there are always 6 digits in X and 7 digits in Y.|||mrjoka said:

the result can be also a string
and:
i want just column X and Y to be converted

So its kinda unclear whether he wants to update or not|||That's assuming there are always 6 digits in X and 7 digits in Y.no it isn't :) :)|||no it isn't :) :)Eh?

-PatP|||yes!! another canadian!!

i should have said "no it isn't, eh!!"|||A somewhat belated congrats on 10K, BTW!

I must have missed something though... I think that a-dam has a point, in that Mr. Joka wants to shear two digits off the left side, not N digits from the right side. I think that you also need one more zero on your second divisor.

-PatP|||"shear two digits off the left side" would mean that 452031 and 5222500 would become 45.2031 and 52.22500 and he wanted 4.52031

my interpretation was that the decimal should be moved 5 positions to the left, so from 452031 and 5222500 to 4.52031 and 52.22500

"move n positions to the left" is the same as "divide by nth power of ten"

you are absolutely right, i did not count to 5 correctly

the revised query isupdate daTable
set x = x / 100000
, y = y / 100000

my "no it isn't" comment was in response to "That's assuming there are always [x] digits"

and of course "move n positions to the left" or "divide by nth power of ten" works just as correctly on, say, a three-digit number like 937

:)|||Without the schema, we'll never know just what Mr Joka has now... Without knowing what he's got now, we can choreograph the angels in their dance, but they'll still be confined to the head of the pin.

Mr Joka, set the angels free! In other words, please give us the schema and the sample data I requested many posts ago.

-PatP|||in post #1 he said they are float|||in post #1 he said they are float...and displayed examples of them as one unformatted string!

...and described the output as possibly being a string too!

-PatP|||actually he said "i want to change them" and of course there is no output from an UPDATE

and he said "the result can be also a string" which i took to mean it's optional

:)

convert dates

dear experts,
i'm working in a website in asp.net where all the data is coming from stored procedures, i have also a callendar, now what i want is that when a client click a date i want to send this value to the stored procedure and the query the database according to that.
i have a day, week, month selection, i did the following in C#
date1(if the client click on a day)
= Calendar1.SelectedDate.ToShortDateString(); this one is simple
but i want to know how to convert the date for the week and the date for the month so the query knows which date and the query the database according to that date.
for the week selection i have just the value stored in DateW = (just the week number) and for the month DateM = (month number 3) how can i convert these dates in sqlserver so the query knows for example if a month selected is march, then the C# value will be 3 and i want the query knows that this is march(from 01-03-2007 till 31-03-2007)?!!!
any suggestions will be very appreciated.
thanks experts.Don't send the bits and pieces of the date string to SQL Server. Convert them to a valic datetime value in C# and then send that to the sproc.|||Hi,

For the first date and the last date of a month check the article at http://www.kodyaz.com/content/FirstAndLastDaysOfMonth.aspx

But also the following select may help you for both month and week

SELECT
firstdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
lastdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1) ,
firstdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0),
lastdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)

Eralper
http://www.kodyaz.com|||thanks for the quick reply,
actually it can not be converted from int to datetime in C# because in C# it is Gregorian calendar which gives you just the week number and the month number, what i want is if there is a way to take this value which is dateM = 3 which is march and convert it in the stored procedure so the query will know that this is march?!!!!
thanks|||Hi,

Do you mean you have only the month and the year parameters?

If so, please check the following sql code,

declare @.y int, @.m int
set @.m = 12
set @.y = 2007

SELECT
firstdate_month =
CAST(
CAST(@.y as varchar(4)) + '/' + CASE WHEN @.m > 9 THEN CAST(@.m as varchar(2)) ELSE '0' + CAST(@.m as char(1)) END + '/01'
as datetime
),
lastdate_month =
DATEADD(dd,-1,
CAST(
CAST(
CASE WHEN @.m < 12 THEN @.y ELSE @.y + 1 END as varchar(4)
) +
'/' +
CASE
WHEN (@.m+1 < 10) THEN '0' + CAST(@.m+1 as char(1))
WHEN (@.m+1 > 9 and @.m+1 < 12) THEN CAST(@.m+1 as varchar(2))
ELSE '01'
END +
'/01'
as datetime
)
)

Eralper
http://www.kodyaz.com|||yes it is something like that, but i already have the query for the database and two parameters, the daystamp is one of them. how can i combine the SP i have and the one you give me? plus how do i need to do the week date, in C# i have just the week number, how can i tell to the SP which date to query the database for example dateW = 13 which is the week 13 of the year?!!
thanks my friend for the help,
good looking|||Hi,

I prepared the following script for the week problem. This is a little bit different when compared with the month version.

But this has to be modified for dates if the week is not in the current year.

declare @.w int
set @.w = 1

SELECT
DATEADD(
dd,
(@.w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
),
DATEADD(
dd,
(@.w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)
)

Eralper
http://www.kodyaz.com|||thanks man for the help,
last question :) i have already a stored procedure that have 2 parameters one of these parameter is the date, how can i combine the sp i already have with the 2 SP you gives me, the @.w is the one i need to give to my stored procedure to query the date, so how can i send the value from the C# code convert it with your sp and then feed it to the my sp to query the database according to the converted date?!!!

thanks