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/ddYou can't change the display if it is the variable is declared as a datetime
datatype with convert. Make it a varchar instead.
> declare @.PubDate VARCHAR(24)
> set @.PubDate = convert(varchar(24),getdate(),111)
Andrew J. Kelly SQL MVP
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:A05FC7F0-DADD-4678-B354-E7B2FA8E8A78@.microsoft.com...
> 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
>|||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 dat
e
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?
"Andrew J. Kelly" wrote:
> You can't change the display if it is the variable is declared as a dateti
me
> datatype with convert. Make it a varchar instead.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:A05FC7F0-DADD-4678-B354-E7B2FA8E8A78@.microsoft.com...
>
>|||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/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching
Andrew J. Kelly SQL MVP
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:6930C3C9-2AD7-4259-A7E9-2D4A575C169D@.microsoft.com...
> 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?
> "Andrew J. Kelly" wrote:
>|||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:
>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
>
>
