Nothing I do changes the format of the date.

declare @PubDate datetime
set @PubDate = convert(varchar,getdate(),111)

    SELECT    @PubDate,*
FROM         OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
DateInserted >= DATE_SUB(@PubDate,INTERVAL 15 DAY) ')

I need it to show the date like yyyy/mm/dd

You can't change the display if it is the variable is declared as a datetime
datatype with convert.  Make it a varchar instead.

Show quoteHide quoteShow quoteHide quote

Thank you. Your answer fixed the formatting issue, but my query doesn't
return any results when it should. It returns results if I hard code the date
in y/m/d format in place of the @PubDate in my query. I tried
convert(datetime, getdate(), 111), but that,  did not solve my problem. Any
other sugestions?

Show quoteHide quote

Well I have no idea what your function DATE_SUB() is doing but you should
have a look at these:


http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching



Show quoteHide quote

Curtis,

I'm surprised this doesn't throw an error, because @PubDate cannot
be used within the OPENQUERY statement.  In addition, since you
have declared @PubDate as datetime, it does not have a format, and
when used where a string is expected, it will be converted using the
default string format.

You have two options, unless you've hidden some secret about
how @PubDate is working in the query:

If you are using SQL Server 2005, you can do this:

EXECUTE(
  N'SELECT ?, * FROM articles
  WHERE Weight = 3 AND DateInserted >= DATE_SUB(?,INTERVAL 15 DAY)',
  @PubDate, @PubDate) at MySQL

You may or may not have to declare @PubDate as a string and pre-convert
it--I don't know what your DATE_SUB function expects.

Alternatively, you can create the entire openquery string dynamically:

DECLARE @sql nvarchar(1000)
DECLARE @PubDate datetime

SET @sql = N'SELECT ''?'', * FROM articles
  WHERE Weight = 3 AND DateInserted >= DATE_SUB(''?'',INTERVAL 15 DAY)'
SET @sql = REPLACE(@sql,'?',CONVERT(varchar,getdate(),111)
EXEC(@sql)

Be absolutely certain that ? is replaced by something you constructed
yourself from a datetime.  Do not let the user provide the substitution
string, or you risk SQL Injection from a maliciously-formed replacement
string.

Steve Kass
Drew University

Curtis wrote:

Show quoteHide quote

Optimal storage of a lot of text records
T Sql Help
Top N worst groups SQL problem
Double Quotes and Print
design question