select to_char(sysdate,'DD-MON-YYYY') from dual;The result would be:
23-FEB-2004How can I get the date formatted this way, especially with the month in character format, from SQL Server?
Working with dates in any DBMS platform can be a challenge and a common need for most DBMS programmers. For SQL Server, Microsoft offered a few different options to address your needs in T-SQL code, which can be incorporated into a stored procedure, batch or script. To capture the date, the appropriate system function from SQL Server would be GETDATE(). As a best practice it is always important to consistently capture the date and time from a single source throughout your application which can be achieved seamlessly via SQL Server.
To capture the date in a script would be achieved as follows:
DECLARE @CurrentDate AS DateTime SELECT @CurrentDate = GETDATE() SELECT @CurrentDate GOCapturing a date and time stamp can also be achieved in an INSERT or UPDATE statement as follows:
-- T-SQL INSERT Example INSERT INTO Table1 (ID, Name, Role, InsertedDate, UpdatedDate) VALUES (1, 'Jeremy K', 'DBA', GETDATE(), NULL) GO -- T-SQL UPDATE Example UPDATE Table1 SET Name = 'Jeremy Kadlec', UpdatedDate = GETDATE() WHERE ID = 1 GORather than including the date values as a portion of a stored procedure, batch or script another option is to create a DEFAULT on a particular column in the table. The example below outlines the default of GETDATE() for the InsertedDate and UpdatedDate columns:
CREATE TABLE [dbo].[Table1] ( [ID] [int] NOT NULL , [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Role] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [InsertedDate] AS (getdate()) , [UpdatedDate] AS (getdate()) ) ON [PRIMARY] GOWith this general date usage information covered, a number of options are available to format the date by issuing the appropriate CAST and CONVERT statements. To format the date in the closest means possible with the simplest piece of code which is based on the default date formats, but without the dashes would be as follows:
SELECT CONVERT (VARCHAR(25), GETDATE(),106)
This response is continued...
This was first published in March 2004