0

Just starting out with SQL, so there's probably a really easy answer here, but I couldn't figure out exactly what i needed to do from a google search.

There's a stored procedure in the database I'm using called dt_char_sp - simply put, the parameters are the date and time as ints, and the stored procedure formats them nicely. I would expect that it works something like this:

EXEC dt_char_sp 20130416, 024356
go

output:

04/06/2013 02:43:56

except the output doesn't happen! I see that there is a variable declared as "@datetime datetime=NULL OUTPUT" and eventually the @datetime variable is filled with the formatted string, but I'm wondering if there's a way to get this output variable?

user1642475
  • 47
  • 6
  • 1
    There's not enough info here to provide assistance. Are you calling this from code (.NET, Java, PHP)? (And if so what language) or using a tool/software like SQL Server Management Studio – David Apr 16 '13 at 19:24

4 Answers4

1

You need to use a Select statement.

At the end of your query just Select the desired output variable like so :

SELECT @datetime

Another way would be to use an Output Parameter. Declared like so : 

@datetime DateTime OUTPUT;
phadaphunk
  • 12,785
  • 15
  • 73
  • 107
  • This doesn't seem to be working... In the stored procedure, @datetime is initially defined as: – user1642475 Apr 16 '13 at 20:47
  • I will imply you are using Sql Server. Go in object explorer/YourDatabase/Programmability/StoredProcedure/ right click on dt_char_sp and modify it. At the very end of the procedure last line add `SELECT @datetime` and run your query to apply changes. If it does not work give me the error – phadaphunk Apr 16 '13 at 20:50
  • Ooooh got it. Is there a way to do this outside the stored procedure? In the stored procedure @datetime is initially defined as: `@datetime DateTime OUTPUT;` Is there a way to reference this "OUTPUT" outside of the stroed proc? i.e. if I execute this in a separate SQL query? something like: `declare @datetime2 datetime execute dt_char_sp 20130401, 111111, @datetime = @datetime2 select @datetime2` – user1642475 Apr 16 '13 at 21:06
  • What you're asking here is if you can [Select from a stored procedure](http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure). Read this post it explains it way better than I could. – phadaphunk Apr 16 '13 at 22:48
1

We cannot be sure without actually seeing the stored procedure, but it sounds like it is using an output parameter.

DECLARE @retval As DATETIME

EXEC dt_char_sp 20130416, 024356, @datetime=@retval OUTPUT

PRINT @retval
go

There is an equivalent way to do this from client code as well.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • Thank you! I was missing the OUTPUT keyword outside of the stored_proc, and that is why this wasn't working. end result was: `declare @datetime2 datetime exec dt_char_sp 20130401, 111111, @datetime=@datetime2 OUTPUT Select @datetime2;` – user1642475 Apr 16 '13 at 22:13
  • @user1642475 Yeah, everyone forgets about it at first. It always seems unnatural/illogical that you have to specify it on *CALL*, but that's how it is. – RBarryYoung Apr 17 '13 at 17:10
0

Are you sure that you are doing something to get a return value? If not, you can select the variable 

select @datetime
matFromArg
  • 11
  • 2
-1

Please do not burden the SQL Server with lame stuff like formatting strings. It's much too important for that kind of thing. I see this a lot lately, and it's a bad habit.

Please, use your client formatting tools for this...

Something like (for UNIX-type dates)

DateTime dt = new DateTime("1/1/1970").AddDays(myIntDate);

Console.Writeln(dt.ToString("mm/dd/yyyy"));

This is better for a lot of reasons - performance is the big one. You have all the data you need already, there is no reason to make a round-trip to a very expensive server when you don't really need anything from it. All computers can format dates, don't use the server for it.

This IS the correct answer by the way. I don't think Stack Overflow should become the place where if someone asks how to jump off a bridge, we just tell them. When someone is asking how to accomplish something USING THE WRONG METHOD, WE SHOULD TELL THEM THE CORRECT METHOD.

Jasmine
  • 4,003
  • 2
  • 29
  • 39
  • -1 this is clearly comment matter. Not even remotly answering the question – phadaphunk Apr 16 '13 at 19:38
  • Hmm, good to know. Unfortunately in the broader context of what I'm trying to do switching the date in a shell script is not appropriate. – user1642475 Apr 16 '13 at 19:42
  • Then you should have said "I'm using a shell script and I need to have the server format strings for me" in your original question. – Jasmine Apr 16 '13 at 21:33