Solved

How getDate() in SP just insert Date?

Posted on 2009-04-03
5
358 Views
Last Modified: 2012-05-06
Hi

I have a stored proc in MSSQL 2005 to insert datetime datatype by getDate()

However, it inserted 2009-04-04 00:53:05.000

How can it just insert 2009-04-04 to the datetime field in the table?
0
Comment
Question by:techques
  • 2
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
catherinelouise earned 25 total points
ID: 24062125
you can use this:
 INSERT dateadd(day, 0, datediff(d,0,getdate()))

Open in new window

0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 25 total points
ID: 24062226
As catherine shows, you can use dateadd to strip the time component.  But when you store it as a datetime value, it will store the time (00:00:00.000) as midnight.  SQL Server 20005 doesn't have a DATE date type.  That wasn't added until SQL 2008.
0
 

Expert Comment

by:Budrophious
ID: 24062449
convert(varchar(50), getdate(), 102)
or
convert(varchar(50), getdate(), 111)
0
 
LVL 5

Expert Comment

by:catherinelouise
ID: 24062643
If the field is a datetime field it will still store the time as 00:00:00:000 with your example Budrophius, as Brandon explains above.  If not, then it will certainly be possible to store just the date.
0
 

Expert Comment

by:Budrophious
ID: 24063270
You are correct catherinelouise.  techques, as already mentioned, the date will be stored as a datetime.  To query the date in the format you requested use one of the convert functions I posted.
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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

19 Experts available now in Live!

Get 1:1 Help Now