DATE function help please - 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

SQL Server Development (2000)

DATE function help please

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-28 : 10:40:55

Hello friends,
I created a view based on an existing table, in this table, the date format is like this: 10/28/2004.
However, on the view, the date returns from the view is like this: 2004-10-28 00:00:00.000,
This is what I use to convert: SELECT CAST(HIRED_DATE AS VARCHAR(30), GETDATE()) FROM EMPLOYEE
BUT it doesn't work.
How do I change this view to have a date format like 10/28/2004???
Thanks,
Jenny.

The stupid question is the question you don't ask.
www.single123.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-28 : 10:48:19

this is not a SQL issue, it is a presentation issue. do the formatting on your web page or in your report or however you are displaying the data.

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-10-28 : 10:48:57

Better to leave the date as a datetime datatype, and do the formatting in the client application, but if you ahve to do it in SQL you need:

SELECT CONVERT(varchar(10), GetDate(), 101)

Kristen

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-28 : 10:55:07

This is what I did, but it still doesn't work.
SELECT CONVERT(HIRED_DATE AS VARCHAR(10), GETDATE(),101)
IT shows errors.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-28 : 11:15:37

SELECT CONVERT(varchar(10), GetDate(), 101) as hired_date

-Jon
Should still be a "Starting Member"

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-28 : 12:11:41

HIRED_DATE is a columnn existed already in the Employee table, not alias.
What you did returns only current date.
Hired_Date
----------
10/8/1999
9/8/2002

This is the format of return dates I want to show up.
Thanks,
Jenny.

The stupid question is the question you don't ask.
www.single123.com

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-28 : 20:36:33

Just replace GETDATE() with your column name

SELECT CONVERT(varchar(10),HIRED_DATE,101) AS MyAlias

BTW QA SQL displays ALL dates like this 2004-10-28 00:00:00.000 (yyyy-mm-dd etc etc) unless converted

Dont be fooled by what EM displays - remember EM is just a GUI

Andy

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-11-01 : 12:32:21

Thanks, hhihihihi.... it worked.
Woh, you are smart.
Thanks again.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources