Solved

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

Posted on 2004-08-10
7
988 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

776 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