Date formatting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date formatting

Author

Topic

malikh
Starting Member

7 Posts

Posted - 2002-08-02 : 15:33:54

How do I get a date value stored on SQL 7.0 to display as "m/d/y" in a query from the stored procedure?

I have the following:

SET DATEFORMAT mdy
GO

DECLARE @datevar datetime

SET @datevar = '12/31/98'

SELECT @datevar
GO

When I run the above in QueryAnalyser, I get

1998-12-31 00:00:00.000

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-02 : 15:37:28

Use CONVERT:

SELECT CONVERT(varchar(10), @datevar, 1)

If that doesn't meet your requirements, you can build a customized string using the DateName and DatePart functions. See Books Online for more details.

Edited by - robvolk on 08/02/2002 15:37:43

malikh
Starting Member

7 Posts

Posted - 2002-08-02 : 15:44:10

quote:

Use CONVERT:

SELECT CONVERT(varchar(10), @datevar, 1)

If that doesn't meet your requirements, you can build a customized string using the DateName and DatePart functions. See Books Online for more details.

Edited by - robvolk on 08/02/2002 15:37:43

I can do the convert, but then its not a date anymore and I cannot sort on it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-02 : 15:50:34

Uhhh....

SELECT Convert(varchar(15), myDateColumn, 1)
FROM myTable
ORDER BY myDateColumn

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-08-08 : 03:28:02

you can use this function to get the date part only

CREATE FUNCTION dbo.justdate (@dttm DATETIME)
RETURNS nvarchar(10)
AS
BEGIN
Declare @udfmmddyyyy nvarchar(10)
SELECT @udfmmddyyyy = RIGHT('0' + CAST(Month(@dttm) AS
Varchar(2)), 2) + '/' + RIGHT('0' + CAST(Day(@dttm) AS
Varchar(2)), 2) + '/' + CAST(Year(@dttm) AS Varchar(4))
RETURN(@udfmmddyyyy)
END

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-08 : 05:14:45

When you want to end up with a datetime value -- if you were grouping as well as ordering by the date parts of values in a datetime column, for example -- I favour:
DATEADD(day, DATEDIFF(day, 0, dateColumn), 0)
It's shorter to type than
CONVERT(datetime, CONVERT(varchar(10), dateColumn, 102), 102)
and a whole lot faster. Viz:


SELECT DATEADD(day, DATEDIFF(day, 0, TestDate), 0) AS dt, COUNT(*) AS ct
INTO #dates1
FROM TestData
GROUP BY DATEADD(day, DATEDIFF(day, 0, TestDate), 0)

TestData has 2171776 rows; 1453 rows get inserted into #dates1. Elapsed time 3.1 secs.
Same with CONVERT syntax: 22.6 secs. Doing just the inner CONVERT and storing the varchar(10): 13.7 seces.

I don't like to mention it, but using dbo.JustDate and storing the nvarchar(10):


SELECT dbo.justdate(TestDate) AS dt, COUNT(*) AS ct
INTO #dates1
FROM TestData
GROUP BY dbo.justdate(TestDate)

Elapsed time: 20.4 minutes

Putting the core of the function (with the nvarchar(10) conversion) inline, 13 secs. I really hope they sort out scalar function call optimization in Yukon!

Edited by - Arnold Fribble on 08/08/2002 06:15:04

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-08 : 08:03:38

milikh,
I think you are confusing the data layer and presentation layer of things here. A datatime is a datetime. It is stored one way by SQL Server (as two 4 byte integers, actually). This is very seperate from how it is displayed. Obviously, when you SELECT GETDATE(), you are not seeing two INTS.

Usually data formatting is handled by your presentation/application layer. Depending on what you are actually trying to accomplish (which incidentally, you never explained) you may want to consider formatting your date in your application.

Just an idea.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources