when i execute an query on date, the date in the result come up with
"2005-06-21 00:00:00:000"  what function I should use to elimate the 0s and
make it comes out as only "2005-06-21"
Thanks,

SELECT CONVERT(CHAR(10), GETDATE(), 120)

However, this is no longer a DATETIME value, it is just a string.  Usually,
date formatting is better handled by the client / presentation tier, not the
query.



Show quoteHide quote

Look up CAST and CONVERT in the SQL Server Books OnLine.  Basically,
you'll need to convert the date value to a char or varchar.

HTH,
Stu

Refer CONVERT function in BOL

E.g

SELECT GETDATE() AS SystemDate,CONVERT(varchar,GETDATE(),101) AS FormattedDate

SystemDate                                             FormattedDate        

------------------------------------------------------
------------------------------
2005-07-08 16:07:37.310                                07/08/2005

(1 row(s) affected)

(1 row(s) affected)

will give you


Show quoteHide quote

Hello qjlee,

Are you wanting to format it different because you can't do so in your front
end app?

At any rate, if you can convert it to a string, then you can use

convert(char(10),yourDateColumn,101)

....which will give you the date in MM/DD/YYYY, which is not exactly what
you want, but close.  Otherwise, you can use the datepart function to get
each part.

-Nick

Show quoteHide quote

Scope of temp table
Why is Count(*) slow?
Bad datetime values stored by SQL Server 2000
Bitwise operations on BigInt
Upsized SQLServer app 100x slower than MsAccess