Solved

How do I store the result of a query into declaration

Posted on 2011-09-15
4
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 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