I am crossing the boundary between Oracle and SQL Server 2000, and I am trying to find information on how to obtain a SQL Server date of 23-FEB-2004. In Oracle, if I query: 
select to_char(sysdate,'DD-MON-YYYY') from dual;
The result would be: 
23-FEB-2004
How can I get the date formatted this way, especially with the month in character format, from SQL Server?

    Requires Free Membership to View

    Login

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
GO
Capturing 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
GO
Rather 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]
GO
With 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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top