Getting rid of the time value in GetDate()

Sorry to be such a dufus but I can't figure out a slick way of getting a date that is just the month, day and year, e.g. 7/1/2003 and still a date data type. I need to edit a DTS package that is slamming data into tables. The previous programmer used GetDate() to insert EnteredOn dates into the tables and the time value is blowing up some of the later processing.

I tried replacing GetDate() with (DatePart(m, GetDate()) + '/' + DatePart(d, GetDate()) + '/' + DatePart(yyyy, GetDate())) AS EnteredOn but that blew up. First it didn't like the '/' strings and if I removed them I got 2011 as my result--AUGH! I also fear that whatever I end up with will not be suitable for stuffing in a date field without then doing a Cast or something.

HELP!

I've tried to use input parameters and a transformation script to change the value of but they all barf on me. Can't use an input parameter in the query, e.g. Select ? as 'EnteredOn' and set the input parameter to the results of VB Date(), either. Changing the value of the input column in a transformation script blows up also.

Grrr! Surely someone out there does this kind of thing on a regular basis?

Thanks for your help!select convert(varchar(10),getdate(),120)

the string gets converted back in to a date when inserted into a datetime/smalldatetime datatype.|||That seems to work. I appreciate the help.

If you're ever in the Denver/Boulder area, I owe you a biplane ride!

Reply to this post!|||careful, I would travel to Denver for a biplane ride!|||Well, give me an email at gailschipper@.yahoo.com if you do, and we'll go.

Cheers!|||This really ought to be a supplied sql server function, it is so common.

Here is the same solution as a User-defined function:

CREATE FUNCTION dbo.DateOnly
(@.RawDateTime datetime )
RETURNS DateTime
AS
BEGIN
RETURN (Convert(Datetime,CONVERT(varchar(10),@.RawDateTime ,101)))
END|||I fully agree! It boggles the mind that it is so difficult to get something so basic. I did pick up the double convert so that the end product is a datetime data type.

Sure wish there was better documentation for these DTS packages. It's putting grey hairs on us VB programmers! After all, we only got into this because it was fun and (relatively) easy.

Thanks for the help!|||DTS is very powerful, but also unwieldy. My personal opinion is that I have never come across a task that had to be done in DTS, and I have never found one that couldn't be more easily implemented and maintained as SQL code, batch files, etc...

I stay away from it.|||Unfortunately, whoever came before me loved them--but wasn't very good at getting the details right. I have miles of DTS editing ahead of me.|||When you take over somebody elses work, you quickly realize that an expert is someone who knows how to use a technology, but a guru is someone who knows when to use a technology!|||I came into SQL Server from Oracle world. I don't have the right words to explain the pain in the transition !!! Anyway one of the first things I realized is the lack of powerful Date functions in SQL Server. Now I have a function that does most of the magic with date columns. If anyone needs it, let me know and I can mail it.|||sbaru Why not zip it up and post it here?sql