Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ilimax
Posting Yak Master
164 Posts |
Posted - 2005-10-17 : 13:22:11
|
Can I say something like thisCREATE PROCEDURE App_POSItems@date_danas datetime=(convert(varchar,getdate,101))ASSET NOCOUNT ONActually, what I want is assigning date value to parameter inside of procedure. I want to assign value of today date in format of 10/17/2005.Does anybody have idea how I can make this.Thanks,Ilimax |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-10-17 : 13:52:00
|
if the parameter's datatype is datetime then assigning the value from convert(varchar,getdate(),101) is a waste of time because sql server will just convert it back to datetime. If you want to use a varchar variable then make the variables datatype varchar. Typically, the database and the stored procedure will accept, use, and return datetimes and the presentation layer will present the dates in whatever format you want.Finally, you wont be able to use an expression as a default value for an SP's input parameter. That will generate an error.Be One with the OptimizerTG |
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2005-10-17 : 14:45:09
|
You want to tell me that whatever value I send to Stored Procedure, Value will be converted into datetime if I assigne variable as datetime ...For example this is my code from ADO ...I am sending format 10/17/2005 ...Set prm_one = myPCmd.CreateParameter("@date_from", adDBTimeStamp, adParamInput, 8, fdate) myPCmd.Parameters.Append prm_oneThis is my procedure ... so value will be transform into date-time value 10/17/2005 12:00:00 PMCREATE PROCEDURE App_POSItems@date_danas datetimeASSET NOCOUNT ON.....So in that case I have to assign variable into Varchar, because my value in table is stored in format 10/17/2005Am I right??? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-10-17 : 15:41:53
|
What is the datatype of the column in your table? If it's datetime then the values are stored as two 4 byte integers, they are not stored as formatted dates.quote: You want to tell me that whatever value I send to Stored Procedure, Value will be converted into datetime if I assigne variable as datetime ...
Yes, see for yourself:declare @d datetimeset @d = convert(varchar, getdate(), 101)select @d [@d], convert(varchar, getdate(), 101) [varchar]set @d = convert(varchar, getdate(), 112)select @d [@d], convert(varchar, getdate(), 112) [varchar] Be One with the OptimizerTG |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-10-17 : 15:45:36
|
What is the problem you're trying to solve? There is probably a simple solution.Be One with the OptimizerTG |
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2005-10-17 : 16:10:40
|
I never think how they are stored but I know how data looks in that colulmn. It's look exactly like this 10/17/2005. However, thanks for lesson.Yes, datatype of columns is datetime but I set default value convert(varchar, getdate(), 101)I am trying to send one date parameter to stored procedure from my VB6 application.That parameter will be always today date.If there is option, I assign that value to variable in stored procedure, that would be fine too... I want to pull data by date column .... date column should be <=today date.Thanks |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-10-17 : 17:51:01
|
Is this the type of logic you're looking for?CREATE PROCEDURE App_POSItems@date_danas datetime = null --you can default it to null so that --if no date was passed then use todayASSET NOCOUNT ON--if no date was passed in then use today's date (at 00:00:00)if @date_danas is null set @date_danas = dateAdd(day, datediff(day,0,getdate()), 0)--your select statementselect <columnList>from <tables>--for all dates equal or prior to today at 11:59:59.999 PMwhere date_danas < dateAdd(day, datediff(day,0,@date_danas)+1, 0) --for all datetimes during today (just today)where dateAdd(day, datediff(day, 0, date_danas), 0) = @date_danas Be One with the OptimizerTG |
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2005-10-18 : 09:26:41
|
I see man there is my lack of knowledge of T-SQL ... I have to read more about sql server ...TG, tell me what do you think about this ...looks like that works ...In this case I do not need to send parameter ...(what I want..)CREATE PROCEDURE App_POSItems--@date_danas datetimeASSET NOCOUNT ONDECLARE @date_danas datetimeSET @date_danas = convert(varchar,getdate(),101)INSERT INTO arhPOSInvoiceItems_2006 ( [Invoice Number] ....However, thanks for replays ....Ilimax |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-10-18 : 17:38:00
|
>>I see man there is my lack of knowledge of T-SQL ... I have to read more about sql server ...We all do Yes, if you will always use today's date the you don't need the input parameter, you can just use a local variable. For your insert statement you don't even need a parameter you can just use an expression. ie:insert arhPOSInvoiceItems_2006 (Invoice_date, <otherColumns>)values (getdate(), <otherColumnValues>)Are you sure you want to lose the precision of the time component in your dates? You can always ignore the times with the techniques above, but you can never use the added precision if you aren't saving them. Be One with the OptimizerTG |
|
|
|
|
|
|
|