Strip Date to just date

How can I take this Date and have it only be 2011-05-11
DECLARE @TodayDate datetime
SET @TodayDate = dateadd(dd, 0, datediff(dd, 0, GETDATE()))

-- Watch = 2011-05-11 00:00:00.000
-- The field in the AS400 is date format *ISO

Open in new window

jaymz69Asked:
Who is Participating?
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
For DATETIME data type you'll always get time part. Which SQL Server are you using 2005 or 2008?
In SQL Server 2008 you have DATE type, so following should work:
DECLARE @TodayDate date
SET @TodayDate = dateadd(dd, 0, datediff(dd, 0, GETDATE()))
SELECT @TodayDate

Open in new window


In 2005 try this:
 
SELECT REPLACE(CONVERT(VARCHAR(10),GETDATE(),111),'/','-')

Open in new window

0
 
jaymz69Author Commented:
I am 2008 r2

I think the AS400 file does not like it when it has the time part in it. So I want to strip it down to the date only
0
 
Daniel_PLDB Expert/ArchitectCommented:
Sorry, you can use ODBC standard:
 
SELECT CONVERT(VARCHAR(10),GETDATE(),120)

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
jaymz69Author Commented:
The only way I get the OPENQURY to work is when I do this:

irdate = ' ' 2011-05-11' '


It does not like anything in the OPENQUERY's WHERE - filed not found in databse AS400

and on the outside WHERE it just runs way too long as if it is getting ALL the data first
0
 
jaymz69Author Commented:
I need to pass the date (of the day it runs) in the OPENQURYso it can gather the transactions to that specific date only

0
 
Daniel_PLDB Expert/ArchitectCommented:
I mean date conversion standard as ODBC, the 120 parameter ;) Since you are working on 2008 just use DATE type.
In case it's not clear do not hesitate to ask.
0
 
jaymz69Author Commented:
This is new to me.

I have done my querying/reporting in Visual Fox Pro 9 (which did SELECts)

what is the date?

the code will help me understand
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.