Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    Red face Unanswered: 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!

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    select convert(varchar(10),getdate(),120)

    the string gets converted back in to a date when inserted into a datetime/smalldatetime datatype.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    Thanks!

    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!

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    careful, I would travel to Denver for a biplane ride!
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    Well, give me an email at gailschipper@yahoo.com if you do, and we'll go.

    Cheers!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  7. #7
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    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!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.

  9. #9
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    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.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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!

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    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.

  12. #12
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    sbaru Why not zip it up and post it here?
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •