Wrox Programmer Forums
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 3rd, 2003, 02:07 PM
Registered User
 
Join Date: Jun 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Dates

Hi All,

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

Thanks,

Treadmill
 
Old July 3rd, 2003, 02:25 PM
Authorized User
 
Join Date: Jun 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 3rd, 2003, 02:30 PM
Authorized User
 
Join Date: Jun 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old July 3rd, 2003, 02:32 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
dates again dhoward VB.NET 2002/2003 Basics 12 August 22nd, 2007 09:48 AM
dates DARSIN General .NET 4 January 14th, 2005 09:09 AM
dates in iis pab006 Classic ASP Basics 2 August 19th, 2004 01:08 AM
between dates capitala Access VBA 1 May 30th, 2004 05:20 PM
Dates oathamm Servlets 1 June 27th, 2003 05:43 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.