Solved

How do I store the result of a query into declaration

Posted on 2011-09-15
4
359 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

810 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