Ask the Expert

Formatting dates, part 1

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:
How can I get the date formatted this way, especially with the month in character format, from SQL Server?

    Requires Free Membership to View

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
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)

-- T-SQL UPDATE Example
SET Name = 'Jeremy Kadlec',
UpdatedDate = GETDATE()
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()) 
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:

This response is continued...

This was first published in March 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: