Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1009
  • Last Modified:

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

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
Westside2004
Asked:
Westside2004
4 Solutions
 
BillAn1Commented:
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
 
jdh0650Commented:
Change the datatype to smalldatetime to get rid of the milliseconds
0
 
BillAn1Commented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Anthony PerkinsCommented:
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
 
hkamalCommented:
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
 
jasonwisdomCommented:
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
 
arbertCommented:
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now