Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    11

    Unanswered: Using default/constraint getdate() function on smalldatetime field

    Hi,

    I have a table with a smalldatetime field and it has a default value of "getdate()".

    mtrInsertedDate -- default: Getdate()

    I have a stored procedure which inserts in the table. The instert statement does not specify the mtrInsertedDate:

    INSERT INTO tbl_Org(mtrID, mtrType, mtrSearchName, groupuid, prvCode) Values(@MatterID, 3, 'New Real Estate Transaction - ' + Cast(@MatterID As varchar(10)), @role, @Prov)

    Now, when I run the stored procedure through Query Analyser, the date is inserted in the field including the time.

    When the application runs and the stored procedure is executed, the insertedDate field contains only the date (the time is always midnight)!?!?! Do you guy have any idea why?

    Thanks for your time,

    Sas

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    If you insert nothing into the field then you will get the default if you specify '', then you will get '1900-01-01 00:00:00'.
    Eample
    Code:
    set nocount on
    drop table abc
    go
    
    create table abc (col1 int, col2 smalldatetime NULL DEFAULT GETDATE())
    go
    
    insert abc (col1) values (1)
    insert abc (col1,col2) values (2,NULL)
    insert abc (col1,col2) values (3,'')
    select * from abc
    go
    OUTPUT
    Code:
    col1        col2                                                   
    ----------- ------------------------------------------------------ 
    1           2002-09-06 10:32:00
    2           NULL
    3           1900-01-01 00:00:00
    MCDBA

  3. #3
    Join Date
    Jul 2002
    Posts
    11
    Actually, I am inserting it like your first insert and I get the right date but the time is set to midnight?!?!?

    BTW, thanks for your reply!

    Originally posted by achorozy
    If you insert nothing into the field then you will get the default if you specify '', then you will get '1900-01-01 00:00:00'.
    Eample
    Code:
    set nocount on
    drop table abc
    go
    
    create table abc (col1 int, col2 smalldatetime NULL DEFAULT GETDATE())
    go
    
    insert abc (col1) values (1)
    insert abc (col1,col2) values (2,NULL)
    insert abc (col1,col2) values (3,'')
    select * from abc
    go
    OUTPUT
    Code:
    col1        col2                                                   
    ----------- ------------------------------------------------------ 
    1           2002-09-06 10:32:00
    2           NULL
    3           1900-01-01 00:00:00

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Could you post a Trace of it, running Profiler?
    MCDBA

Posting Permissions

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