Q

Formatting dates, part 1

This Content Component encountered an error
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?

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

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close