Solved

How to format getDate() when using it for default value

Posted on 2004-08-10
7
995 Views
Last Modified: 2012-06-21
Hi,

I wanted to ask.. I have a column in my db where I use getDate()

In Enterprise Manager I just went to the area where it says default value and put in "getDate()".  Not sure if this is the way to achieve what I want....

Each time a record is inserted into this table it automatically populates a column called "DateCreated" with the value returned from getDate()

However, the format comes like this:

2004-08-10 12:49:42.780

How can I use it the same way but have my date/time formatted like:

08-10-2004 2:35 AM  (I don't NEED the AM, but it would be nice to know if the time was AM or PM)

Can anyone help with the above?  

Thanks

-West
0
Comment
Question by:Westside2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 17

Assisted Solution

by:BillAn1
BillAn1 earned 25 total points
ID: 11767936
internally, the date is stored as anumber, the problem is on the disply side.
If you want to convert it to a particular format you can use the CONVERT function,
e.g. CONVERT( varchar(20), mydate, 101) etc this will make it look the way you want.
0
 
LVL 1

Assisted Solution

by:jdh0650
jdh0650 earned 25 total points
ID: 11767943
Change the datatype to smalldatetime to get rid of the milliseconds
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11767968
JUst checked, you are actually looking for format 20 yyyy-mm-dd HH:mi:ss
take a look at the help within QueryAnalyser for explanation of all the different formats available.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 25 total points
ID: 11768010
There is no style, that I know of in T-SQL that will give you exaclty this format:
08-10-2004 2:35 AM

You can combine styles from CONVERT and concatenate, but this seems like overkill.  I suggest you handle in the front-end.
0
 
LVL 5

Expert Comment

by:hkamal
ID: 11768234
declare @a int, @sql varchar(100)  select @a=100
while @a<116 begin select @sql="select @a, convert(varchar, getdate(), "+convert(varchar,@a)+")" exec (@sql) select @a=@a+1 end

if none of these styles suites you, you can write a function to do your own
0
 
LVL 1

Accepted Solution

by:
jasonwisdom earned 50 total points
ID: 11768244
You can concatenate together a string (varchar) of DATEPART functions to get your desired format.

DECLARE @datenow datetime
DECLARE @datestring varchar(50)
SELECT @datenow=getdate()
SELECT @datestring=CAST(DATEPART(mm, @datenow) AS varchar) + '-' + CAST(DATEPART(dd, @datenow) AS VARCHAR) + '-' + CAST(DATEPART(yyyy, @datenow) AS VARCHAR) + ' ' + CAST(DATEPART(hh, @datenow) AS VARCHAR) + ':' + CAST(DATEPART(mm, @datenow) AS VARCHAR)
SELECT @datestring

It's a bit rough, but if you need an exact (non-standard) date format then piecing it together is the way to go.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11768246
Agree with acperkins.  No built in convert to give you what you want--more difficult formats are usually better suited for your frontend...
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

626 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