Solved

How do I store the result of a query into declaration

Posted on 2011-09-15
4
348 Views
Last Modified: 2012-05-12
SQL SERVER (not reporting)

I need to store the results of a query, into a declaration, not actually run it.

For example:
Declare
@mydate datetime

Select
@mydate = select top1  somdatefield from sometable

How do I store the result of the query into @mydate, in date format?

What I have found so far only executes the query in varchar format and doesn't store it.  

Thanks.



0
Comment
Question by:Ursino
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
25112 earned 500 total points
ID: 36546807
2 examples for you

Declare @mydate datetime =  (select top 1  somdatefield from sometable)
Select @mydate

Declare @mydate datetime =  GETDATE()
select @mydate

(these work in 2008). In 2005 it will be

Declare @mydate datetime
Set @mydate =  (select top 1  somdatefield from sometable)
Select @mydate
0
 
LVL 5

Expert Comment

by:25112
ID: 36546815
as long as somdatefield  is indeed a date field, @mydate will also be datefield only.

try this:

Declare @mydate datetime =  (select top 1  somdatefield from sometable)
Select cast(@mydate as datetime)
select @mydate
0
 
LVL 25

Expert Comment

by:jogos
ID: 36547554
For converting a non-datefield to datetime or other way around : cast or convert
http://msdn.microsoft.com/en-us/library/ms187928.aspx
But the less convertion the better.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36547936
Declare @mydate datetime

Select @mydate = (select top1  somdatefield from sometable)

the brackets around the subQuery are required

or

Declare @mydate datetime
 select top1  @mydate =somdatefield from sometable


but in any case the Query has to RUN to obtain the value
and for the top to have any meaning (an obtain consistent results) you must specify an order by clause .
unless you wish to select a random value?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Troubleshooting Methodology - steps 3 36
BULK LOGGED - log full 9 25
SQL Stored Procedure insert running but not inserting record 40 61
CROSS APPLY 4 43
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A short film showing how OnPage and Connectwise integration works.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now