locked
date as GETDATE() or CURRENT_TIMESTAMP RRS feed

  • Question

  • This should be something relatively simple.

    I have set a data type as datetime. I am now trying to have the date and time put in each cell as data is entered.

    I have tried GETDATE() or CURRENT_TIMESTAMP through the Check Constraints GUI but am getting an error msg:

    'Statistics' table
    - Error validating check constraint 'CK_Statistics'

    Any ideas?

    Friday, May 18, 2012 11:27 AM

Answers

  • CREATE TABLE [dbo].[Statistics2]([DT] [datetime] NOT NULL)
    GO
    ALTER TABLE [dbo].[Statistics2] ADD  DEFAULT (CURRENT_TIMESTAMP) FOR [DT]
    GO
    INSERT INTO [dbo].[Statistics2] VALUES (DEFAULT)
    GO 
    SELECT * FROM [dbo].[Statistics2]
    GO
    --DROP TABLE [dbo].[Statistics2]

    Jon


    • Edited by Jon Gurgul Friday, May 18, 2012 12:49 PM added --
    • Marked as answer by meridius10 Friday, May 18, 2012 2:21 PM
    Friday, May 18, 2012 12:48 PM

All replies

  • Hi, you should use DEFAULT constraint. Check this link:

    Working with DEFAULT constraints in SQL Server

    David.
    • Proposed as answer by Naomi N Friday, May 18, 2012 1:09 PM
    Friday, May 18, 2012 11:34 AM
  • You need to provide more information; (1) provide the table definition including the check constraint and (2) provide the query that is causing the error.  As far as which is preferred, getdate() or current_Timestamp I would suggest at this time to use Current_Timestamp because it is oriented to the ANSI standard.

    However, understand that the one that you are likely to more frequently see at this time is getdate() because getdate() has been around for 20 years.  Getdate() will translate into Sybase but not Oracle or DB2.

    Friday, May 18, 2012 12:01 PM
  • You have created another constraint on Statistics table. Have a check on that constraint and see whats wrong with that.

    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Friday, May 18, 2012 12:07 PM
  • CREATE TABLE [dbo].[Statistics2]([DT] [datetime] NOT NULL)
    GO
    ALTER TABLE [dbo].[Statistics2] ADD  DEFAULT (CURRENT_TIMESTAMP) FOR [DT]
    GO
    INSERT INTO [dbo].[Statistics2] VALUES (DEFAULT)
    GO 
    SELECT * FROM [dbo].[Statistics2]
    GO
    --DROP TABLE [dbo].[Statistics2]

    Jon


    • Edited by Jon Gurgul Friday, May 18, 2012 12:49 PM added --
    • Marked as answer by meridius10 Friday, May 18, 2012 2:21 PM
    Friday, May 18, 2012 12:48 PM
  • Thanks. This T-SQL resolved the problem:

    ALTER TABLE [dbo].[Statistics] ADD  DEFAULT (CURRENT_TIMESTAMP) FOR [date]

    I think I had put dbo.Statistics and not [dbo].[Statistics] i.e. no squared brackets.

    Interestingly if I try to Create the Constraint with the GUI I get this code:

    USE [PitcherStats3Table]GOALTER TABLE [dbo].[Statistics] ADD  DEFAULT (getdate()) FOR [Date]GO

    so it replaces

    CURRENT_TIMESTAMP

    with

    getdate()

    The time/date appears after the table has been reopened and not after data is entered in a row and the cursor is moved down to the following row so I presume this is normal behaviour.

    In terms of other constraints in the table structure there is of course the primary key and the not nulls but I'm nor sure how this would have an effect on this.

    For some reason this was still rejected when I tried to add this through the GUI method and I am not sure why.

    The most important thing though is that the constraint has been created through T-SQL and is working.

    Friday, May 18, 2012 1:42 PM
  • If you wanted to add a default via SSMS you would need to right click on the table and click "Design" and then in "General" alter "Default Value or Binding" with the approriate column highlighted.

    The constraints folder when right-clicked "new constraint" starts a check constraint dialog looking specifically to create check constraints.

    You are right that CURRENT_TIMESTAMP is indeed replaced with getdate().

    http://connect.microsoft.com/SQLServer/feedback/details/125745/using-current-timestamp-current-user-is-automatically-translated-to-getdate-user-name-when-creating-a-script


    Jon


    • Edited by Jon Gurgul Friday, May 18, 2012 1:56 PM
    Friday, May 18, 2012 1:55 PM
  • Thanks. Just putting CURRENT_TIMESTAMP or getdate() into "Default Value or Binding" is a more automated way to do this!


    • Edited by meridius10 Friday, May 18, 2012 2:21 PM
    Friday, May 18, 2012 2:21 PM