getdate()

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
>
>

getdate SQL Convert to Long Format

,convert(varchar,getdate(),101) as [CONFIRMATION_DATE!1!REPORT_DATE]

The above displays as 8/26/2006, anyway you can convert that to a long format in the SP?

I.E. August 26, 2006

Thanks.

i think you are using c sharp code.
i dont know about c sharp. but in vb we do it like this

dim dt as string
dt = Format(Now, "MMMM DD,yyyy")

hope that it might help you.

|||Does thishttp://www.sql-server-helper.com/tips/date-formats.aspx help?|||

Why not simply using this:

select CONVERT(varchar,getdate())

For more information about converting DATETIME to various styles, please refer to the first table in this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

|||

thanks for the replies. The sql helper worked great.

get the rows where the info from one table is not contained in the

hello,
i have 2 tables which have 2 fields.
Common in the 2 tables is the id, the other field is a varchar(256)
example
Table1
Id UserInfo
1 Pc A.Julien-3400
2 Soft V.Noris-2800
3 Liz Barbara -2345
Table2
Id Username
1 Julien
2 Jack
3 Barbara
I want to get the id value where the username is not contained in the UserIn
fo
In the example
for id=1 Julien is contained in Pc A.Julien-3400
for id=2 Jack !!! is not contained .....
for id=3 Barbara is contained in Liz Barbara -2345
For this case i want to get only id=2
thanks
best regardsYou can do something like:
SELECT
<your column list>
FROM
table1
JOIN table2 ON table1.id = table2.id AND CHARINDEX(table2.col,
table1.col) > 0
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:1DB6FF37-9D4A-4A9A-A486-6E0C382F0070@.microsoft.com...
> hello,
> i have 2 tables which have 2 fields.
> Common in the 2 tables is the id, the other field is a varchar(256)
> example
> Table1
> Id UserInfo
> 1 Pc A.Julien-3400
> 2 Soft V.Noris-2800
> 3 Liz Barbara -2345
> Table2
> Id Username
> 1 Julien
> 2 Jack
> 3 Barbara
> I want to get the id value where the username is not contained in the
> UserInfo
> In the example
> for id=1 Julien is contained in Pc A.Julien-3400
> for id=2 Jack !!! is not contained .....
> for id=3 Barbara is contained in Liz Barbara -2345
>
> For this case i want to get only id=2
> thanks
> best regards|||On Wed, 1 Feb 2006 06:52:27 -0800, Xavier wrote:

>hello,
>i have 2 tables which have 2 fields.
>Common in the 2 tables is the id, the other field is a varchar(256)
>example
>Table1
>Id UserInfo
>1 Pc A.Julien-3400
>2 Soft V.Noris-2800
>3 Liz Barbara -2345
>Table2
>Id Username
>1 Julien
>2 Jack
>3 Barbara
>I want to get the id value where the username is not contained in the UserI
nfo
>In the example
>for id=1 Julien is contained in Pc A.Julien-3400
>for id=2 Jack !!! is not contained .....
>for id=3 Barbara is contained in Liz Barbara -2345
>
>For this case i want to get only id=2
Hi Xavier,
SELECT Table1.Id, Table1.UserInfo, Table2.UserName
FROM Table1
INNER JOIN Table2
ON Table2.Id = Table1.Id
WHERE Table1.UserInfo NOT LIKE '%' + Table2.Username + '%'
Hugo Kornelis, SQL Server MVP

Get the 3 max age

Hi,
I have a table with
ID int,
Name varchar,
Age int
how could i make an stored procedure to get the three old persons from
database?
--
Thanks
Regards.
JosemaTry:
select top 3 * from TheTable
order by Age desc|||Wouldn't it make more sense to store date of birth rather than age? How
will you keep the Age column up to date?
SELECT TOP 3 WITH TIES id, name, age
FROM YourTable
ORDER BY age DESC
Presumably there could be several people with the same age so you may
well get more than 3 rows returned. Do you have a rule for the 3 you
want in the event of ties? You can add other columns to the ORDER BY
clause to narrow down the selection.
David Portas
SQL Server MVP
--|||Thanks bd,
Regards.
Josema
"bd" wrote:

> Try:
> select top 3 * from TheTable
> order by Age desc
>|||> Wouldn't it make more sense to store date of birth rather than age? How
> will you keep the Age column up to date?
Heh... run a job every day that joins against a linked server (e.g. the IRS
database) on SSN, and checks if their DOB has the same day and month as
today. I'm sure it gets even more interesting on Feb. 29. :-)|||>> Heh... run a job every day that joins against a linked server
And for those who are into astrological beliefs, the job has to be scheduled
to run every minute or perhaps every sec!
Anith

Get table warnings?

Recently I added a varchar(8000) to a table through enterprise manager.
Everything went fine, no errors/warnings reported.
Thought I should script the change, got a warning through query analyzer
that row exceeded 8060. That I understand just fine.
Being no expert, I'm wondering how I can check my tables for warnings. DBCC
checktable reports nothing wrong on the table with row length > 8060.
I know I can manually check other tables for the row length, but I'm
concerned about other warnings I may have introduced.
Thanks,
MikeThough SQL Server lets you create a table with row size > 8060, you are
still limited by the size 8060. So, your table is just like any other table,
and SQL Server will not let you have a data row that exceeds that limit.
The DBCC commands you are talking about are mostly for checking database
consistency.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:uHy50X25DHA.1368@.TK2MSFTNGP10.phx.gbl...
Recently I added a varchar(8000) to a table through enterprise manager.
Everything went fine, no errors/warnings reported.
Thought I should script the change, got a warning through query analyzer
that row exceeded 8060. That I understand just fine.
Being no expert, I'm wondering how I can check my tables for warnings. DBCC
checktable reports nothing wrong on the table with row length > 8060.
I know I can manually check other tables for the row length, but I'm
concerned about other warnings I may have introduced.
Thanks,
Mike

Get table warnings?

Recently I added a varchar(8000) to a table through enterprise manager.
Everything went fine, no errors/warnings reported.
Thought I should script the change, got a warning through query analyzer
that row exceeded 8060. That I understand just fine.
Being no expert, I'm wondering how I can check my tables for warnings. DBCC
checktable reports nothing wrong on the table with row length > 8060.
I know I can manually check other tables for the row length, but I'm
concerned about other warnings I may have introduced.
Thanks,
MikeThough SQL Server lets you create a table with row size > 8060, you are
still limited by the size 8060. So, your table is just like any other table,
and SQL Server will not let you have a data row that exceeds that limit.
The DBCC commands you are talking about are mostly for checking database
consistency.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:uHy50X25DHA.1368@.TK2MSFTNGP10.phx.gbl...
Recently I added a varchar(8000) to a table through enterprise manager.
Everything went fine, no errors/warnings reported.
Thought I should script the change, got a warning through query analyzer
that row exceeded 8060. That I understand just fine.
Being no expert, I'm wondering how I can check my tables for warnings. DBCC
checktable reports nothing wrong on the table with row length > 8060.
I know I can manually check other tables for the row length, but I'm
concerned about other warnings I may have introduced.
Thanks,
Mike

Get rows for latest date

Hello!

I have a table something like this:

ID INTEGER

Info VARCHAR (actually several columns but that is not important here)

DAT DateTime

For each ID there are several dates and for each of these dates there are several rows with different info. I would like to select the latest info for each ID. For example:

ID - DAT - Info

1 - 2007-02-01 - Info1

1 - 2007-02-01 - Info2

1 - 2006-02-01 - Info3

2 - 2007-05-05 - Info4

2 - 2007-02-01 - Info5

2 - 2006-02-01 - Info6

I would like to get:

Info1

Info2

Info4

This has to be done in one Query. Can anybody help me?

Here it is,

Code Block

Create Table #data (

[ID] int ,

[DAT] datetime ,

[Info] Varchar(100)

);

Insert Into #data Values('1','2007-02-01','Info1');

Insert Into #data Values('1','2007-02-01','Info2');

Insert Into #data Values('1','2006-02-01','Info3');

Insert Into #data Values('2','2007-05-05','Info4');

Insert Into #data Values('2','2007-02-01','Info5');

Insert Into #data Values('2','2006-02-01','Info6');

select main.info from #data main

join (select ID,max(dat) dat from #data group by ID) as latest

on latest.ID=main.ID and latest.dat=main.dat

|||

A couple of options:

Code Block

create table testdata

(ID int, Dat DATETIME, Nm CHAR(5))

INSERT INTO testdata

SELECT 1, '1 feb 2007', 'Info1'

UNION ALL

SELECT 1, '1 feb 2007', 'Info2'

UNION ALL

SELECT 1, '1 feb 2006', 'Info3'

UNION ALL

SELECT 2, '5 may 2007', 'Info4'

UNION ALL

SELECT 2, '1 feb 2007', 'Info5'

UNION ALL

SELECT 2, '1 feb 2006', 'Info6'

--SQL2005

WITH cte

AS

(SELECT ID, Nm, RANK() OVER (PARTITION BY ID ORDER BY Dat DESC) AS D

FROM testData)

SELECT Nm

FROM cte

WHERE D = 1

--SQL2000

SELECT Nm

FROM

(SELECT ID, MAX(Dat) AS Dt

FROM testData

GROUP BY ID) AS Bob

INNER JOIN testData t

ON Bob.Dt = t.Dat AND Bob.ID = t.ID

HTH!|||

One more trick..

Code Block

--SQL2005

;with cte

as

(select id, nm,dat,max(dat) over (partition by id) as latestdat from testdata)

select nm

from cte

where dat = latestdat

Code Block

--SQL Server 2000

select

main.info

from

#data main

where

exists

(

select * from

(

select

d

,max(dat) dat

from

#data

group by D

) data

where

data.d = main.d

and data.dat=main.dat

)

|||

Thank You guys!!

...for the fast and helpful response. I would never figure that out.

Get rid of leading 0s of a varchar field

Hi,
I have a field called StreetNo in a table called Prospects, the field is a
varchar(10). The table is populated by someone else, so I have no control
over what kind of data entered into the table. The StreetNo I got is a
complete mess, e.g. 00001, 01234, 01234a, 0000 PO Box. Is there a SQL
statement that I can use to show the StreetNo without the leading 0s?
I tried:
select Case when StreetNo is null then '' else convert(varchar(10),
convert(integer, StreetNo)) + ' ' end
This is not working for 01234a or 0000 PO Box
TIA
TIABetter to use a 3rd party product or a programming language for data
cleanup. With t-SQL, given all the data values are messed up, you can try
something like:
SELECT STUFF( @.s, 1, PATINDEX( '%[^0]%', @.s ) - 1, SPACE(0) )
Anith