locked
Create Table RRS feed

  • Question

  • Can some quickly help me out pls.

    How to create a date of birth that will only show the day and the month without showing the year by using t-sql

    Example: 30 June

    Monday, October 3, 2011 8:10 AM

Answers

  • create table test(dob datetime not null) ;
    
    insert into test(dob)
    select '20000106' union all
    select '20010207' union all
    select '20020308' union all
    select '20030409' union all
    select '20040510' ;
    
    select
         day ( dob ) as dob_day
    ,    datename ( mm , dob ) as dob_month
    from test
    
    

    • Marked as answer by kingdomkev Monday, October 3, 2011 9:42 AM
    Monday, October 3, 2011 9:24 AM
  • >>>Pls how do I add CHECK constraints so that day is between  1and 31? 
    create table #t (c int constraint mychk check (c>=1 and c<=31))
    insert into #t select 1 --ok
    insert into #t select 32 --failed
    >>>And how do I use a leap year?

    declare @d datetime
    set @d='20030610'
    select datestring, case
      when isdate(datestring/100*100+31) = 1 then 31
      when isdate(datestring/100*100+30) = 1 then 30
      when isdate(datestring/100*100+29) = 1 then 29
      when isdate(datestring/100*100+28) = 1 then 28
    end
    from (
      select convert(varchar,@d,112) as datestring
    ) D
    ------------------------------------
    declare @d datetime
    set @d = getdate() -- or the date in question

    select 32-day(@d-day(@d)+32)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by kingdomkev Wednesday, November 2, 2011 6:59 PM
    Monday, October 31, 2011 1:31 PM
  • Hi kingdomkev,

    I am getting the following errors:

    (Msg 8152, Level 16, State 14, Line 4



    String or binary data would be truncated.

    The statement has been terminated).

    Ah yes. Probably SQL Server's least helpful error message. SQL Server
    is telling you "I found a string that was longer than the declared
    length of the column, but I'm not going to tell you which one it is.
    Nah-nana-nana-nah!"

    The cause, in this case, is the postal code: 'M3 4PQ' is six
    characters (the space is a character as well), but the column is
    declared ass char(5).

    And while you're at it making changes, change the day and month values
    too. Use 2 and 11 instead of '2' and '11'. Using the quotes will make
    this string constants. They will be converted to tinyint and then
    stored, so the end result is the same, but it's less efficient.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by kingdomkev Wednesday, November 2, 2011 6:58 PM
    Wednesday, November 2, 2011 12:34 PM
  • You have an ID column that should auto increment. But in your statement you are inserting into it.E.g, the  "MemberID INT NOT NULL IDENTITY(1,1)," cannot be inserted into.

     

    CREATE TABLE PPP.Members (
    
    MemberID INT NOT NULL IDENTITY(1,1),
    
    GivingID INT NOT NULL,
    
    Title CHAR (5) NOT NULL,
    
    FirstName VARCHAR (30)  NULL,
    
    LastName VARCHAR (30) NOT NULL,
    
    Gender CHAR (7) NOT NULL,
    
    MaritalStatus CHAR (7) NOT NULL,
    
    Occupation VARCHAR (30) NOT NULL,
    
    BirthDate DATETIME NULL,
    
    BirthMonth DATETIME NULL,
    
    EmailAddress VARCHAR (50) NULL,
    
    AddressLine1 VARCHAR (60)NULL,
    
    AddressLine2 VARCHAR (60) NULL,
    
    CITY VARCHAR (50)NULL,
    
    PostalCode CHAR (5) NULL,
    
    Country VARCHAR (50)NULL,
    
    PhoneNumber VARCHAR (50) NOT NULL,
    
    FirstTimer CHAR (3) NOT NULL,
    
    StartDate DATETIME DEFAULT '01/31/2004' NULL,
    
    IsCurrent CHAR (1) NULL,
    
    CONSTRAINT PK_Members PRIMARY KEY (MemberID),
    
    CONSTRAINT FK_Giving FOREIGN KEY (GivingID) REFERENCES PPP.Members (MemberID),
    
    CONSTRAINT CHK_BirthDate CHECK (BirthDate > =1),
    
    CONSTRAINT CHK_BirthMonth CHECK (BirthMonth < =31));
    
    GO
    
     
    
    SET IDENTITY_INSERT PPP.Members ON
    
     
    
    INSERT INTO PPP.Members (GivingID,Title, FirstName, LastName, Gender, MaritalStatus, Occupation, BirthDate, BirthMonth, EmailAddress, AddressLine1, AddressLine2, City, PostalCode, Country, FirstTimer, StartDate, IsCurrent)
    
    VALUES ( 1, 'Mr.', 'Kelly', 'Brown', 'M', 'Single', 'Teacher', '02', '11', 'brownk@ymail.com',  '25 Longvale Drive', 'Clayton', 'Manchester', 'M3 4PQ', 'England', 'N', '09-12-2010', 'Y')
    
    GO 
    
     
    
    SET IDENTITY_INSERT PPP.Members OFF
    
    GO


     


    • Edited by saintgr8 Thursday, November 10, 2011 10:02 PM
    • Proposed as answer by saintgr8 Thursday, November 10, 2011 10:02 PM
    • Marked as answer by kingdomkev Friday, November 11, 2011 10:23 AM
    Thursday, November 10, 2011 10:00 PM

All replies

  • Do you want to store the full DOB on your table, but only display day and month on your selects,

    or only store day and month?

    Monday, October 3, 2011 8:34 AM
  • Hi John Ang, just the day and month but both would really appreciated.

    Thanks

    Monday, October 3, 2011 8:46 AM
  • create table test(dob datetime not null) ;
    
    insert into test(dob)
    select '20000106' union all
    select '20010207' union all
    select '20020308' union all
    select '20030409' union all
    select '20040510' ;
    
    select
         day ( dob ) as dob_day
    ,    datename ( mm , dob ) as dob_month
    from test
    
    

    • Marked as answer by kingdomkev Monday, October 3, 2011 9:42 AM
    Monday, October 3, 2011 9:24 AM
  • SELECT DATENAME(DAY,GETDATE())+' '+DATENAME(MONTH,GETDATE())
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 3, 2011 9:25 AM
  • Wow! nice one and thanks a lot John Ang.
    Monday, October 3, 2011 9:42 AM
  • Thanks for the comments:

    Currently, a client wants my team to create a column in a table that stores the months and dates (e g, 12th 0f April without the year) of births of it customers as many will only give that information and not the year.

    Please help

    Wednesday, October 19, 2011 2:06 PM
  • Hi kingdomkev,

    How to create a date of birth that will only show the day and the month without showing the year by using t-sql

    Example: 30 June

    SQL Server has no data type that allows you to do this.

    Possible workarounds are:

    1. Use two columns, one for day and one for months. Add CHECK
    constraints so that day is between 1 and 31, month is a valid month
    name (or a number between 1 and 12, your choice), and an extra CHECK
    constraint to prevent dates like April 31 or February 30.

    2. Use a normal date column but strip out the year in the presentation
    layer. The actual year stored is irrelevant. Just make sure to use a
    leap year, otherwise a birthday of Feb 29 will be rejected.

    3. Use CLR to create your own custom data type with .Net code. You can
    try searching open source code libraries to see if one already exists;
    otherwise pick up a copy of SQL Server MVP Deep Dives Volume 2
    (http://www.manning.com/delaney/) and use the information in chapter
    23 to get a head start on this.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Wednesday, October 19, 2011 10:08 PM
  • Pls how do I add CHECK constraints so that day is between  1and 31? 

    And how do I use a leap year?

    Pls give example as to make it easy for me.

     

    Thanks

    Monday, October 31, 2011 1:20 PM
  • >>>Pls how do I add CHECK constraints so that day is between  1and 31? 
    create table #t (c int constraint mychk check (c>=1 and c<=31))
    insert into #t select 1 --ok
    insert into #t select 32 --failed
    >>>And how do I use a leap year?

    declare @d datetime
    set @d='20030610'
    select datestring, case
      when isdate(datestring/100*100+31) = 1 then 31
      when isdate(datestring/100*100+30) = 1 then 30
      when isdate(datestring/100*100+29) = 1 then 29
      when isdate(datestring/100*100+28) = 1 then 28
    end
    from (
      select convert(varchar,@d,112) as datestring
    ) D
    ------------------------------------
    declare @d datetime
    set @d = getdate() -- or the date in question

    select 32-day(@d-day(@d)+32)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 31, 2011 1:31 PM
  • >>>Pls how do I add CHECK constraints so that day is between  1and 31? 
    create table #t (c int constraint mychk check (c>=1 and c<=31))
    insert into #t select 1 --ok
    insert into #t select 32 --failed
    >>>And how do I use a leap year?

    declare @d datetime
    set @d='20030610'
    select datestring, case
      when isdate(datestring/100*100+31) = 1 then 31
      when isdate(datestring/100*100+30) = 1 then 30
      when isdate(datestring/100*100+29) = 1 then 29
      when isdate(datestring/100*100+28) = 1 then 28
    end
    from (
      select convert(varchar,@d,112) as datestring
    ) D
    ------------------------------------
    declare @d datetime
    set @d = getdate() -- or the date in question

    select 32-day(@d-day(@d)+32)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by kingdomkev Wednesday, November 2, 2011 6:59 PM
    Monday, October 31, 2011 1:31 PM
  • Hi kingdomkev,

    Pls how do I add CHECK constraints so that day is between  1and 31? 

    Check Books Online. I think the syntax for CHECK constraints is in the
    CREATE TABLE subject, if it's not you'll find a link to it there.

    And how do I use a leap year?

    In the front end where the user enters a date, create a string
    consisting of that date and the year 2008, then convert that to the
    appropriate date data type in the front end language. This should all
    be done before even sending anything to SQL Server. If you need more
    exact help, you need to ask help in a group for whatever language the
    front-end is written in.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by Naomi NEditor Monday, October 31, 2011 6:47 PM
    Monday, October 31, 2011 5:59 PM
  • Hello Uri Dimant,

    Thank you very much for your concern.

    Trying to INSERT INTO  the  table below but I am getting following error:

     

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting date and/or time from character string.

    Any help please?

     

     

    CREATE TABLE PPP.Members (

    MemberID INT NOT NULL IDENTITY(1,1),

    GivingID INT NOT NULL,

    Title CHAR (5) NOT NULL,

    FirstName VARCHAR (30)  NULL,

    LastName VARCHAR (30) NOT NULL,

    Gender CHAR (7) NOT NULL,

    MaritalStatus CHAR (7) NOT NULL,

    Occupation VARCHAR (30) NOT NULL,

    BirthDate DATETIME NULL,

    BirthMonth DATETIME NULL,

    EmailAddress VARCHAR (50) NULL,

    AddressLine1 VARCHAR (60)NULL,

    AddressLine2 VARCHAR (60) NULL,

    CITY VARCHAR (50)NULL,

    PostalCode CHAR (5) NULL,

    Country VARCHAR (50)NULL,

    PhoneNumber VARCHAR (50) NOT NULL,

    FirstTimer CHAR (3) NOT NULL,

    StartDate DATETIME DEFAULT '01/31/2004' NULL,

    IsCurrent CHAR (1) NULL,

    CONSTRAINT PK_Members PRIMARY KEY (MemberID),

    CONSTRAINT FK_Giving FOREIGN KEY (GivingID) REFERENCES PPP.Members (MemberID),

    CONSTRAINT CHK_BirthDate CHECK (BirthDate > =1),

    CONSTRAINT CHK_BirthMonth CHECK (BirthMonth < =31));

    GO

     

    SET IDENTITY_INSERT PPP.Members ON

     

    INSERT INTO PPP.Members (MemberID,GivingID,Title, FirstName, LastName, Gender, MaritalStatus, Occupation, BirthDate, BirthMonth, EmailAddress, AddressLine1, AddressLine2, City, PostalCode, Country, FirstTimer, StartDate, IsCurrent)

    VALUES (1, 1, 'Mr.', 'Kelly', 'Brown', 'M', 'Single', 'Teacher', '02', '11', 'brownk@ymail.com',  '25 Longvale Drive', 'Clayton', 'Manchester', 'M3 4PQ', 'England', 'N', '09-12-2010', 'Y')

    GO 

     

    SET IDENTITY_INSERT PPP.Members OFF

    GO

     

    Tuesday, November 1, 2011 8:54 AM
  • Hi kingdomkev,

    If you want to store only day and only month in the BirthDate and
    BirthMonth columns, you'll have to define them as either tinyint (and
    insert numeric values, such as 3 or 28), or char(2) (and insert string
    values, such as '03' or '28'). You now have them defined as datetime,
    so SQL Server will attempt to convert the values '02' and '11' in your
    query to a valid datetime value - and fail.

    Also, for the default and the value in the INSERT statement for the
    StartDate column, I suggest using the 'YYYYMMDD' format to prevent
    possible conversion errors ('MM/DD/YYYY' and 'DD/MM/YYYY' are both
    ambiguous).


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Tuesday, November 1, 2011 1:48 PM
  • Hi Hugo Kornelis, 

     

    I did changed the BirthDate and BirthMonth to TINYINT

    and also the StartDate of the INSERT TO  YYYYMMDD

    I am getting the following errors:

    (Msg 8152, Level 16, State 14, Line 4

    String or binary data would be truncated.

    The statement has been terminated).

    Any help pls.

    Below is the following table  queries

    Thanks

     

    IF OBJECT_ID('PPP.Members' ) IS NOT NULL BEGIN

     DROP TABLE PPP. Members;

     END;

     

    CREATE TABLE PPP.Members (

    MemberID INT NOT NULL IDENTITY(1,1),

    GivingID INT NOT NULL,

    Title CHAR (5) NOT NULL,

    FirstName VARCHAR (30)  NULL,

    LastName VARCHAR (30) NOT NULL,

    Gender CHAR (7) NOT NULL,

    MaritalStatus CHAR (7) NOT NULL,

    Occupation VARCHAR (30) NOT NULL,

    BirthDate TINYINT NULL,

    BirthMonth TINYINT NULL,

    EmailAddress VARCHAR (50) NULL,

    AddressLine1 VARCHAR (60)NULL,

    AddressLine2 VARCHAR (60) NULL,

    CITY VARCHAR (50)NULL,

    PostalCode CHAR (5) NULL,

    Country VARCHAR (50)NULL,

    PhoneNumber VARCHAR (50) NOT NULL,

    FirstTimer CHAR (3) NOT NULL,

    StartDate DATETIME DEFAULT '2004-06-01' NULL,

    IsCurrent CHAR (1) NULL,

    CONSTRAINT PK_Members PRIMARY KEY (MemberID),

    CONSTRAINT FK_Giving FOREIGN KEY (GivingID) REFERENCES PPP.Members (MemberID),

    CONSTRAINT CHK_BirthDate CHECK (BirthDate > =1),

    CONSTRAINT CHK_BirthMonth CHECK (BirthMonth < =31));

    GO

     

    SET IDENTITY_INSERT PPP.Members ON

     

    INSERT INTO PPP.Members (MemberID,GivingID,Title, FirstName, LastName, Gender, MaritalStatus, Occupation, BirthDate, BirthMonth, EmailAddress, AddressLine1, AddressLine2, City, PostalCode, Country, FirstTimer, StartDate, IsCurrent)

    VALUES (1, 1, 'Mr.', 'Kelly', 'Brown', 'M', 'Single', 'Teacher', '2', '11', 'brownk@ymail.com',  '25 Longvale Drive', 'Clayton', 'Manchester', 'M3 4PQ', 'England', 'N', '20101101', 'Y')

    GO 

     

    SET IDENTITY_INSERT PPP.Members OFF

    GO

    Wednesday, November 2, 2011 10:32 AM
  • Hi kingdomkev,

    I am getting the following errors:

    (Msg 8152, Level 16, State 14, Line 4



    String or binary data would be truncated.

    The statement has been terminated).

    Ah yes. Probably SQL Server's least helpful error message. SQL Server
    is telling you "I found a string that was longer than the declared
    length of the column, but I'm not going to tell you which one it is.
    Nah-nana-nana-nah!"

    The cause, in this case, is the postal code: 'M3 4PQ' is six
    characters (the space is a character as well), but the column is
    declared ass char(5).

    And while you're at it making changes, change the day and month values
    too. Use 2 and 11 instead of '2' and '11'. Using the quotes will make
    this string constants. They will be converted to tinyint and then
    stored, so the end result is the same, but it's less efficient.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by kingdomkev Wednesday, November 2, 2011 6:58 PM
    Wednesday, November 2, 2011 12:34 PM
  • Hello Hugo,

    I really want to say a very big thank you for your help and concern.

    This indeed solved the problem.

    Once again I say thanks.

     

    Wednesday, November 2, 2011 6:58 PM
  • Hello Hugo,

    I am able to resolved the issue following your instructions. 

    I can INSERT INTO the Members table but I am having the

    following errors at the rest of the two tables and the errors says:

    ==============================================================

    Msg 1088, Level 16, State 11, Line 25

    Cannot find the object "Giving" because it does not exist or you do not have permissions.

    Msg 544, Level 16, State 1, Line 2

    Cannot insert explicit value for identity column in table 'Giving' when IDENTITY_INSERT is set to OFF.

    Msg 544, Level 16, State 1, Line 5

    Cannot insert explicit value for identity column in table 'Giving' when IDENTITY_INSERT is set to OFF.

    Msg 1088, Level 16, State 11, Line 9

    Cannot find the object "Giving" because it does not exist or you do not have permissions.

    Msg 544, Level 16, State 1, Line 22

    Cannot insert explicit value for identity column in table 'Workers' when IDENTITY_INSERT is set to OFF.

    ==========================================================================

     

    Below are my tables, Pls your sincere will really be appreciated

    Thanks

     

    -- Create database

     

    CREATE DATABASE PreciousPeopleParish;

     

    GO

     

    USE PreciousPeopleParish;

    GO

    --------------------------------------------------------

    CREATE SCHEMA PPP AUTHORIZATION dbo;

    GO

    --------------------------------------------------------

     

     

    IF OBJECT_ID('PPP.Members' ) IS NOT NULL BEGIN

     DROP TABLE PPP. Members;

     END;

     

    CREATE TABLE PPP.Members (

    MemberID INT NOT NULL IDENTITY,

    GivingID INT NOT NULL,

    Title CHAR (5) NOT NULL,

    FirstName VARCHAR (30)  NULL,

    LastName VARCHAR (30) NOT NULL,

    Gender CHAR (7) NOT NULL,

    MaritalStatus CHAR (10) NOT NULL,

    Occupation VARCHAR (30) NOT NULL,

    BirthDate TINYINT NULL,

    BirthMonth TINYINT NULL,

    EmailAddress VARCHAR (50) NULL,

    AddressLine1 VARCHAR (60)NULL,

    AddressLine2 VARCHAR (60) NULL,

    CITY VARCHAR (50)NULL,

    PostalCode CHAR (10) NULL,

    Country VARCHAR (50)NULL,

    PhoneNumber VARCHAR (50) NOT NULL,

    FirstTimer CHAR (3) NOT NULL,

    StartDate DATETIME DEFAULT '20040601' NULL,

    IsCurrent CHAR (1) NULL,

    CONSTRAINT PK_Members PRIMARY KEY (MemberID),

    CONSTRAINT FK_Members_Giving FOREIGN KEY (GivingID) REFERENCES PPP.Members (MemberID),

    CONSTRAINT CHK_BirthDate CHECK (BirthDate > =1),

    CONSTRAINT CHK_BirthMonth CHECK (BirthMonth < =31));

    GO

     

    SET IDENTITY_INSERT PPP.Members ON

     

    INSERT INTO PPP.Members (MemberID,GivingID,Title, FirstName, LastName, Gender, MaritalStatus, Occupation, BirthDate, BirthMonth, EmailAddress, AddressLine1, AddressLine2, City, PostalCode, Country,PhoneNumber, FirstTimer, StartDate, IsCurrent)

    VALUES ( 1,1, 'Mr.', 'Kelly', 'Brown', 'M', 'Single', 'Teacher', 2, 11, 'brownk@ymail.com',  '25 Longvale Drive', 'Clayton', 'Manchester', 'M3 4PQ', 'England', '07940494035', 'N', '20101101', 'Y')

     

    INSERT INTO PPP.Members (MemberID,GivingID,Title, FirstName, LastName, Gender, MaritalStatus, Occupation, BirthDate, BirthMonth, EmailAddress, AddressLine1, AddressLine2, City, PostalCode, Country,PhoneNumber, FirstTimer, StartDate, IsCurrent)

    VALUES ( 2,2, 'Mrs.', 'Jane', 'Peters', 'F', 'Married', 'Nurse', 30, 12,'jpet@yahoo.com', '89 Chris Street', 'Moston', 'Bolton', 'B12 4DX','England', '07403766990', 'N', '20090528', 'Y')

     

    INSERT INTO PPP.Members (MemberID,GivingID,Title, FirstName, LastName, Gender, MaritalStatus, Occupation, BirthDate, BirthMonth, EmailAddress, AddressLine1, AddressLine2, City, PostalCode, Country,PhoneNumber, FirstTimer, StartDate, IsCurrent)

    VALUES   (3,3, 'Mr.', 'Kenny', 'West', 'M', 'Married', 'Engineer', 27, 10, 'kwest1@yahoo.com', '22 Beevers Close', 'Newton Health','Manchester', 'M40 9HF', 'England', '07506977000', 'Y', '20111030', 'Y')

     

    GO 

     

    SET IDENTITY_INSERT PPP.Members OFF

    GO

     

    SELECT * FROM PPP.Members

     

    -- CREATE GIVING TABLE

     

    IF OBJECT_ID('PPP.Giving' ) IS NOT NULL BEGIN

     DROP TABLE PPP.Giving;

     END;

     

    CREATE TABLE PPP.Giving (

    GivingID INT NOT NULL IDENTITY,

    MemberID INT NOT NULL,

    Offering MONEY NULL,

    Tithe MONEY NULL,

    PoorHomelss MONEY NOT NULL,

    Thanksgiving MONEY NULL,

    Pledge MONEY NOT NULL,

    Mission MONEY NOT NULL,

    FirstFruit MONEY NOT NULL,

    GuestSpeaker MONEY NOT NULL,

    CONSTRAINT PK_Giving PRIMARY KEY (GivingID),

    CONSTRAINT FK_Giving_Members FOREIGN KEY (MemberID)

    REFERENCES PPP.Members (MemberID));

     

    SET IDENTITY_INSERT Giving ON

    GO

     

    INSERT INTO PPP.Giving ( GivingID, MemberID, Offering, Tithe, PoorHomelss, Thanksgiving,Pledge, Mission, FirstFruit,GuestSpeaker)

    VALUES (1,1,'10', '50', '2', '20', '5', '1', '100', '10')

     

    INSERT INTO PPP.Giving ( GivingID, MemberID, Offering, Tithe, PoorHomelss, Thanksgiving,Pledge, Mission, FirstFruit,GuestSpeaker)

    VALUES (2,2, '15', '25', '40', '2', '35', '5', '300', '15')

     

     

    SET IDENTITY_INSERT Giving OFF

    GO

     

    -- CREATE WORKERS TABLES

     

    IF OBJECT_ID('PPP.Workers' ) IS NOT NULL BEGIN

     DROP TABLE PPP.Workers;

     END;

     

    CREATE TABLE PPP.Workers (

    WorkerID INT NOT NULL IDENTITY,

    GivingID INT NULL,

    FirstName VARCHAR (30)NULL,

    LastName VARCHAR (30) NOT NULL,

    MeetingDate DATETIME, 

    Department VARCHAR(30) NULL,

    WorkersContribution MONEY NULL,

    StartDate DATETIME DEFAULT '20050602' NULL,

    IsCurrent CHAR (1) NULL,

    CONSTRAINT PK_Workers PRIMARY KEY (WorkerID),

    CONSTRAINT FK_Worker_Giving FOREIGN KEY (GivingID) REFERENCES PPP.Giving (GivingID)); 

     

     

    INSERT INTO PPP.Workers( WorkerID, GivingID, FirstName, LastName, MeetingDate, Department, WorkersContribution, StartDate, IsCurrent)

    VALUES ( 1, 1, 'James', 'West', '29-10-2011', 'Drama', '2', '27-10-2010', 'Y')

    GO

    Thursday, November 3, 2011 10:53 AM
  • Hi kingdomkev,

    I can INSERT INTO the Members table but I am having the

    following errors at the rest of the two tables and the errors says:

    Are you using SQL Server Management Studio to test your code? If so,
    you can double-click any error message and you will immediately be
    positioned at (or near) the location of the error.

    If you do that for this batch, you'll find that the first error comes
    from a line where the schema prefix of the Giving table is omitted.
    The error caused this statement to not be executed, which explains the
    second and third error message.

    In this way, you should e able to easily find and correct all errors
    in your script.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, November 3, 2011 12:10 PM
  • Hi Hugo,

    I have already solved the problems.

    Thanks.

    Thursday, November 3, 2011 3:10 PM
  • Hello Hugo,

    I am having a start date or an end date that looks like  this  one  2010-10-27 00:00:00.000.

    Is there any way I can get rid of the  zeros by having something like this  2010-10-27?

     

    Thanks

    Monday, November 7, 2011 8:31 AM
  • Hi Uri Dimant,

    Thank a lot for your answers.

    Please I wanted my BirthMonth to be something like this (December) instead of (12)

    How would I do it?

    Thanks

     

    Wednesday, November 9, 2011 8:18 PM
  • You have an ID column that should auto increment. But in your statement you are inserting into it.E.g, the  "MemberID INT NOT NULL IDENTITY(1,1)," cannot be inserted into.

     

    CREATE TABLE PPP.Members (
    
    MemberID INT NOT NULL IDENTITY(1,1),
    
    GivingID INT NOT NULL,
    
    Title CHAR (5) NOT NULL,
    
    FirstName VARCHAR (30)  NULL,
    
    LastName VARCHAR (30) NOT NULL,
    
    Gender CHAR (7) NOT NULL,
    
    MaritalStatus CHAR (7) NOT NULL,
    
    Occupation VARCHAR (30) NOT NULL,
    
    BirthDate DATETIME NULL,
    
    BirthMonth DATETIME NULL,
    
    EmailAddress VARCHAR (50) NULL,
    
    AddressLine1 VARCHAR (60)NULL,
    
    AddressLine2 VARCHAR (60) NULL,
    
    CITY VARCHAR (50)NULL,
    
    PostalCode CHAR (5) NULL,
    
    Country VARCHAR (50)NULL,
    
    PhoneNumber VARCHAR (50) NOT NULL,
    
    FirstTimer CHAR (3) NOT NULL,
    
    StartDate DATETIME DEFAULT '01/31/2004' NULL,
    
    IsCurrent CHAR (1) NULL,
    
    CONSTRAINT PK_Members PRIMARY KEY (MemberID),
    
    CONSTRAINT FK_Giving FOREIGN KEY (GivingID) REFERENCES PPP.Members (MemberID),
    
    CONSTRAINT CHK_BirthDate CHECK (BirthDate > =1),
    
    CONSTRAINT CHK_BirthMonth CHECK (BirthMonth < =31));
    
    GO
    
     
    
    SET IDENTITY_INSERT PPP.Members ON
    
     
    
    INSERT INTO PPP.Members (GivingID,Title, FirstName, LastName, Gender, MaritalStatus, Occupation, BirthDate, BirthMonth, EmailAddress, AddressLine1, AddressLine2, City, PostalCode, Country, FirstTimer, StartDate, IsCurrent)
    
    VALUES ( 1, 'Mr.', 'Kelly', 'Brown', 'M', 'Single', 'Teacher', '02', '11', 'brownk@ymail.com',  '25 Longvale Drive', 'Clayton', 'Manchester', 'M3 4PQ', 'England', 'N', '09-12-2010', 'Y')
    
    GO 
    
     
    
    SET IDENTITY_INSERT PPP.Members OFF
    
    GO


     


    • Edited by saintgr8 Thursday, November 10, 2011 10:02 PM
    • Proposed as answer by saintgr8 Thursday, November 10, 2011 10:02 PM
    • Marked as answer by kingdomkev Friday, November 11, 2011 10:23 AM
    Thursday, November 10, 2011 10:00 PM