Date format

[catalystcommercial]

Hi there

Can anyone tellme the code to bring back all the data > than May 2005 in the following format DD/MM/YYYY and I also need to remove the time stamp

Thanks

Tim

 

[bborissov]

SELECT *, convert(varchar(10),DateField, 103) AS MyDateFieldAsVarcharWithOutTimePortion from MyTable
       WHERE DateField > '20050531'

Borislav Borissov

 

[SQLDenis]

WHERE DateField > '20050531'
will bring back May 31 (after midnight)
you need to change that to
WHERE DateField >= '20050601'
or include the time
like this
WHERE DateField > '2005-05-31 23:59:59.997'


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Just thought about that and have problesm with INet

Borislav Borissov

 

[bborissov]

Just thought about that and have problems with INet

Borislav Borissov

 

[catalystcommercial]

Hi guys

Thanks for your reply, not quite sure it is what I want

I want to change the date format and remove the time stamp...

This is my query:

select key1,company,ZIP,uenappsp,uenappby,UENTOP5,UENBOT55,UENSUP5,UENORDEV5,UENEXPPAY5,UENPAYREC5,UENCONST5,uenconsign,uenamrec5
from contact1 left outer join contact2
on contact1.accountno=contact2.accountno
where uentoP5 is not null
and UENCONST5 is not null
and UENEXPPAY5 > 'May1,05'
and UENCONST5 != 'OBJ'
and UENCONST5 != 'REJ'
and UENCONST5 != 'CAN'
and UENCONST5 != 'CON'

 

[SQLBill]

In the SELECT line, you need to make this change:

change this: UENEXPPAY5
to this:
CONVERT(VARCHAR(10), UENEXPPAYS, 101)
[code]

Refer to the BOL, use the index tab and enter CONVERT.

-SQLBill

Posting advice: FAQ481-4875