[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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

0
jaymz69
Asked:
jaymz69
  • 4
  • 3
1 Solution
 
Daniel_PLDB 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now