Solved

# GetDate??

Posted on 2006-04-21
1,955 Views
I have a query in SQL server 2005 that I run every day.  Can I change the values below to pull the date from the system rather than having to change them each day?

set @rundate = '2006-04-21'   --  Today
set @startdate = '2006-04-20 12:00:00'  --Yesterday 12 PM
set @enddate = '2006-04-20 12:00:00'  --Today 12 PM

The Start and End dates have to use 12:00 PM

Can I use the GetDate function to set this up??

Scotto13
0
Question by:Scotto13

LVL 142

Expert Comment

I assume that 12 PM means at lunch time, not at midnight...

set @rundate = getdate()
set @startdate = dateadd(hour, -12, convert(datetime, convert(varchar(10), @rundate, 120), 120))
set @enddate = dateadd(day, 1, @startdate )

0

LVL 75

Accepted Solution

select @today = convert(varchar(10),getdate(),120),
@startDate   =  CAST(convert(varchar(10),getdate(),120)+' 12:00:00' as datetime) -1 ,
@endDate    = CAST(convert(varchar(10),getdate(),120)+' 12:00:00' as datetime)
0

Author Comment

Yes, 12 PM is Noon.

0

LVL 75

Expert Comment

select @today = convert(varchar(10),getdate(),120),
@startDate   =  CAST(convert(varchar(10),getdate(),120)+' 12:00:00' as datetime) -1 ,
@endDate    = CAST(convert(varchar(10),getdate(),120)+' 12:00:00' as datetime)
0

Author Comment

ok.  that last one works.
0

Author Comment

Thanks Aneesh!
0

## Featured Post

### Suggested Solutions

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.