Solved

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

Posted on 2004-08-10
7
989 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

791 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