Solved

How do I store the result of a query into declaration

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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