?
Solved

GetDate() Format Output

Posted on 2005-04-06
8
Medium Priority
?
1,633 Views
Last Modified: 2009-04-08
I have a trigger that inserts the date into a table. Everything is working fine except the date is not formatted how I want it.
INSERT INTO DateTable
                      (dateTime)
VALUES     (GETDATE())

It is formatted like this
Apr  6 2005  3:07PM

And I would like it like this
04/06/05 15:07

Thanks
0
Comment
Question by:lyptus
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 17

Expert Comment

by:mokule
ID: 13720989
You can't format datetime field.
You can onle format displaying it
0
 

Author Comment

by:lyptus
ID: 13721031
The field named dateTime is type nvarchar. I made it nvarchar so I could format it.
0
 
LVL 6

Assisted Solution

by:peyox
peyox earned 100 total points
ID: 13721041
Here's article about formating date fileds:
http://www.databasejournal.com/features/mssql/article.php/2197931
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Accepted Solution

by:
mokule earned 600 total points
ID: 13721056
Then You can format it like this
select CONVERT(varchar(20),getdate(),1)+' '+ CONVERT(varchar(5),getdate(),14)
0
 
LVL 17

Expert Comment

by:mokule
ID: 13721117
That is You should have
INSERT INTO DateTable
                      (dateTime)
VALUES     (CONVERT(varchar(20),getdate(),1)+' '+ CONVERT(varchar(5),getdate(),14))
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 13721153
Getdate() returns a datetime value. How that is formatted is based on your regional options in the control panel.

Something like

CONVERT(varchar(12), getdate(), 1) + ' ' + CONVERT(varchar(5), getdate(), 114)

Should work.
0
 

Author Comment

by:lyptus
ID: 13721186
Thanks mokule,
How would I modify that to include the whole year?
so
04/06/2005 15:07
0
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 300 total points
ID: 13721216
Change it to

CONVERT(varchar(12), getdate(),101) + ' ' + CONVERT(varchar(5), getdate(), 114)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question