Solved

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

Posted on 2004-08-10
7
987 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now