locked
DateTime datatype....I JUST WANT THE DATE PORTION RRS feed

  • Question

  • I am currently using datetime as my datatype for a column in a simple table. I am using the getdate() function as a property in the Default value section so it will automatically place today's date when a new record is created. How do I get this to add/show ONLY THE DATE PORTION? Is there a datatype I'm not familiar with?

    Thanks for your help!


    DR
    Thursday, July 15, 2010 5:30 PM

Answers

  • Try:

    declare @T table (c1 datetime not null default (dateadd([day], datediff([day], '19000101', getdate()), '19000101')));

    insert into @T default values;

    select * from @T;

     

    AMB

    Thursday, July 15, 2010 6:15 PM
  • Hi,

    Adding to AMB.. if you are using SQL server 2008 then you can use "DATE" data type which allows only date part.

    and to insert in DATETIME Column only Date Part you can also use this:

    SELECT CONVERT(DATE, GETDATE())

    if you are using Sql 2005/2000.

    Try this:

    DECLARE @TempDate TABLE
    (
        Today DATETIME
    )
    --
    INSERT INTO @TempDate
    SELECT CONVERT(DATETIME, CONVERT(VARCHAR(12), GETDATE(), 101))
    --
    SELECT * FROM @TempDate

    Thanks,

    Sandeep

    Friday, July 16, 2010 5:47 AM
  • IMO , this page is required reading when using dates in SqlServer

    http://www.karaszi.com/SQLServer/info_datetime.asp


    Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/
    Friday, July 16, 2010 7:42 AM

All replies

  • Try:

    declare @T table (c1 datetime not null default (dateadd([day], datediff([day], '19000101', getdate()), '19000101')));

    insert into @T default values;

    select * from @T;

     

    AMB

    Thursday, July 15, 2010 6:15 PM
  • Hi,

    Adding to AMB.. if you are using SQL server 2008 then you can use "DATE" data type which allows only date part.

    and to insert in DATETIME Column only Date Part you can also use this:

    SELECT CONVERT(DATE, GETDATE())

    if you are using Sql 2005/2000.

    Try this:

    DECLARE @TempDate TABLE
    (
        Today DATETIME
    )
    --
    INSERT INTO @TempDate
    SELECT CONVERT(DATETIME, CONVERT(VARCHAR(12), GETDATE(), 101))
    --
    SELECT * FROM @TempDate

    Thanks,

    Sandeep

    Friday, July 16, 2010 5:47 AM
  • IMO , this page is required reading when using dates in SqlServer

    http://www.karaszi.com/SQLServer/info_datetime.asp


    Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/
    Friday, July 16, 2010 7:42 AM
  • Thanks AMP, Sandeep and Dave for your responses here!

    I should have mentioned 2 things:

    1) I am not a database admin or designer by trade. I suspect you already know this...LOL. I was crazy enough to take on the assignment of developing a database for our production workflow at this plant. I've actually begun to enjoy it and aspire to be a semi-professional some day...again, LOL.

    2) I am indeed using SQL Express 2005

    FOR AMP: If my table name is SHIP_LOG and the column name is LogEntryDate, how would your code read? Also, where should I place it? Is it a stored proc or should I place the code under general properties in table builder, Management Studio?

    FOR Sandeep: I'm considering upgrading to SQL Server just to solve this problem. Is upgrading as simple as connecting to a back up of my current 2005 version? My real issue here is my front end app won't recognize the filter I've placed on a report that generates based on user input of any given day. I hoping this will solve it.

    FOR Dave: Thank you for this link. I will read and hopefully have a better understanding of this process.

    Kindest regards to all! More to come

    Divad


    DR
    Friday, July 16, 2010 3:28 PM