Hi All,

It is possible to convert getDate() into the format dd/mm/yyyy without the time hanging on the end?

Thanks,

Treadmill

CONVERT(char(10), GETDATE(), 103)

this will give you a charater string of length 10 in the format you requested. See "Cast and Convert" in the SQL Server 2000 Books On-line. You can use convert to format a date any way imaginable.

-Mike

By the way this won't work on non SQL Server DBMS's. I'm not very familiar with other databases but there's bound to be a similar function. If not there should at least be a function to extract the month, day, and year from a date expression. Using these functions you can just concatinate them together in any order with any separator.

Sorry for not inculding this on the first post.

Getdate(), like the datetime datatypes, always contains both the date and the time. You cannot change this, that's just the way the datetime datatypes work.

You can, however, choose to ignore the time if you wish. You can use the CONVERT function to convert a datetime into a character string, and if the string is short enough, then only the date will be converted into the string. You can return the string as a column in the resultset, or if you need a datetime datatype, then you can CAST that string back to a datetime and that will result in the time being set to zero (or more properly, midnight):

SELECT CONVERT(varchar(8),Getdate(),112)

will return a string in the form yyyymmdd. See BOL for the CONVERT function for other formatting values (the third parameter).

SELECT CAST(CONVERT(varchar(8),Getdate(),112) as datetime)

will return a datetime datatype consisting of midnight on the current day.

Note that you can use a similar technique to ignore the date and only use the time portion.

As mentioned by the poster above, the CONVERT function is proprietary to SQL Server. The CAST function, while considerably less flexible than CONVERT, is standard SQL and should be available on all self-respecting implementations. If you CAST a datetime to a string, the result is in the form 'mmm dd,yyyy hh:mmPM', so using varchar(11) will just strip off the date.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com