locked
Convert function doesn't work RRS feed

  • Question

  • Hello team,

    I created a table and I want to insert values:

    CREATE TABLE tbl_All_Members
    (
     MemberID int PRIMARY KEY NOT NULL
    ,FirstName nVarchar (50) NOT NULL
    ,LastName nVarchar (100) NOT NULL
    ,DateOFBirth Date
    ,DateOFBirthASText nVarchar (20)
    ,AGE int
    ,Gender nVarChar(2)
    ,PolicyHolderMemberID nVarchar(200) NOT Null
    )

    ==============================
    INSERT INTO tbl_All_Members
     (
      MemberID
     ,FirstName
     ,LastName
     ,DateOFBirth
     ,Convert(Nvarchar(12), DateOfBirth) AS DateOFBirthASText
     ,DateDiff(Y, DateofBirth, GetDate()) AS AGE
     ,(CASE
        When zipcode % 2 = 1 Then 'M'
        ELSE 'F'
      END) AS GENDER
     ,Concat('01', ' ', MemberID) AS PolicyHolderMmeberID
     ,GetDate() AS CoverageEffDt
     ,Concat(FirstName, ' ' , LastName) AS FullName
     ,ZipCode
     )
     VALUES(1, 'Mary', 'Jackson', '01/01/1988', 92677);

    DateOfBirth has a date data type and I want to convert the DateOfBirth, it gives errors:

    Convert(Nvarchar(12), DateOfBirth) AS DateOFBirthASText

    What is the problem?

    Thanks,

    CloudsInSky


    CloudsInSky

    Tuesday, April 28, 2020 1:19 AM

Answers

  • Hi cloudsInSky, 

    Your code has some problems with syntax usage. Please  check following script . 

    CREATE TABLE tbl_All_Members
    (
     MemberID int PRIMARY KEY NOT NULL
    ,FirstName nVarchar (50) NOT NULL
    ,LastName nVarchar (100) NOT NULL
    ,DateOFBirth Date
    ,DateOFBirthASText nVarchar (20)
    ,AGE int
    ,Gender nVarChar(2)
    ,PolicyHolderMemberID nVarchar(200) NOT Null
    )
    
    declare @test table (
     MemberID int PRIMARY KEY NOT NULL
    ,FirstName nVarchar (50) NOT NULL
    ,LastName nVarchar (100) NOT NULL
    ,DateOFBirth Date
    ,ZipCode int
    )
    insert into @test
     VALUES(1, 'Mary', 'Jackson', '01/01/1988', 92677);
    
    insert into tbl_All_Members 
    select  MemberID
     ,FirstName
     ,LastName
     ,DateOFBirth
     ,Convert(Nvarchar(12), DateOfBirth) AS DateOFBirthASText
     ,DateDiff(Y, DateofBirth, GetDate()) AS AGE
     ,(CASE
        When zipcode % 2 = 1 Then 'M'
        ELSE 'F'
      END) AS GENDER
     ,ZipCode 
    from @test
    
    select* from tbl_All_Members
    /*
    MemberID    FirstName  LastName DateOFBirth DateOFBirthASText    AGE         Gender PolicyHolderMemberID
    ----------- ---------- -------------------- -------------------- ----------- ------ --------------------------
    1           Mary       Jackson  1988-01-01  1988-01-01           11806       M      92677
    
    */

    Best Regards,

    Rachel 



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by cloudsInSky Tuesday, April 28, 2020 7:55 PM
    Tuesday, April 28, 2020 2:26 AM
  • Hi,

    Please check this one . 

    IF OBJECT_ID('tbl_All_Members') IS NOT NULL drop table  tbl_All_Members   
    go 
    
    CREATE TABLE tbl_All_Members
    (
     MemberID int PRIMARY KEY NOT NULL
    ,FirstName nVarchar (50) NOT NULL
    ,LastName nVarchar (100) NOT NULL
    ,DateOFBirth Date
    ,DateOFBirthASText nVarchar (20)
    ,AGE int
    ,Gender nVarChar(2)
    ,PolicyHolderMemberID nVarchar(200) NOT Null
    )
    
    ALTER Table tbl_All_Members ADD CoverageEffDt Date;
    ALTER TAble tbl_All_Members ADD FullName nVarChar(150);
    ALTER TABLE tbl_All_Members ADD ZipCode int;
    
    
    declare @test table (
     MemberID int PRIMARY KEY NOT NULL
    ,FirstName nVarchar (50) NOT NULL
    ,LastName nVarchar (100) NOT NULL
    ,DateOFBirth Date
    ,ZipCode int
    )
    insert into @test
     VALUES(1, 'Mary', 'Jackson', '01/01/1988', 92677);
    
    insert into tbl_All_Members 
    select  MemberID
     ,FirstName
     ,LastName
     ,DateOFBirth
     ,Convert(Nvarchar(12), DateOfBirth) AS DateOFBirthASText
     ,DateDiff(Y, DateofBirth, GetDate()) AS AGE
     ,(CASE
        When zipcode % 2 = 1 Then 'M'
        ELSE 'F'
      END) AS GENDER,
      Concat('01', ' ', MemberID) AS PolicyHolderMmeberID
     ,GetDate() AS CoverageEffDt
     ,Concat(FirstName, ' ' , LastName) AS FullName
     ,ZipCode 
    from @test
    
    select* from tbl_All_Members
    /*
    MemberID    FirstName      LastName    DateOFBirth DateOFBirthASText    AGE         Gender PolicyHolderMemberID   CoverageEffDt FullName       ZipCode
    ----------- -------------- ----------- ----------- -------------------- ----------- ------ ---------------------- ------------- -------------- -----------
    1           Mary           Jackson     1988-01-01  1988-01-01           11806       M      01 1                   2020-04-28    Mary Jackson   92677
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by cloudsInSky Tuesday, April 28, 2020 7:56 PM
    Tuesday, April 28, 2020 5:43 AM
  • To answer your question, the INSERT statement syntax you posted is incorrect. 

    Please see:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15

    The correct syntax is "INSERT INTO TABLENAME (columnname1, columnname2, ....)".  You cannot specify conversions in the column list.

    You also cannot do functions like CONVERT in the "VALUES" section, without using SELECT. 

    So the correct syntax is:

    insert into tbl_All_Members (
    	MemberID
    	,FirstName
    	,LastName
    	,DateOFBirth
    	,DateOFBirthASText
    	,AGE
    	,Gender
    	,PolicyHolderMemberID
    	)
    select 
    	1 
     ,'Mary'
     ,'Jackson'
     ,'01/01/1988'
     ,Convert(Nvarchar(12), '01/01/1988')
     ,DateDiff(Y, '01/01/1988', GetDate())
     ,(CASE
        When 92677 % 2 = 1 Then 'M'
        ELSE 'F'
      END) AS GENDER
     ,92677


    • Edited by Tom Phillips Tuesday, April 28, 2020 6:16 PM
    • Marked as answer by cloudsInSky Tuesday, April 28, 2020 7:51 PM
    Tuesday, April 28, 2020 6:15 PM
  • Everything you've posted is wrong. The prefix "TBL_"  is a design flaw so bad that it has a name; it is called a "tibble". But since member_member_firstname table models a complete set up some kind of entity you made this even worse by adding that "All_" prefix. A table name should be a collective or plural noun since it does model a set.

    Next, member ID is based on a nominal scale since it's an identifier. But by definition, an identifier cannot be a numeric because you do know math on it. I also have to ask how many people you know whose names are over 150 characters long in Unicode.  Really? The international postal Union convention is that a line and address should be 35 or fewer characters. This is based on a 3.5 inch pressure label that was in industry-standard.  

    The whole goal of databases, not just SQL, was to reduce in redundancy data that could be computed from existing columns is never materialized in a good SQL schema. Please think about it for a minute; you can compute the results faster than you can read them off the disk, as well as saving disk space.

    If you learned ISO standards, you would know there is no such thing as gender; it's actually called the sex code and is a single character. But it does not have to be in Mandarin Chinese. 

    CREATE TABLE Membership
    (member_id CHAR(10) NOT NULL PRIMARY KEY,
     member_firstname NVARCHAR  (35) NOT NULL,
     member_lasttname  NVARCHAR  (35) NOT NULL,
     birth_date DATE,
     sex_code CHAR (1) NOT NULL DEFAULT '0'
         CHECK (sex_code IN ('0', '1', '2', '9'),
     policy_holder_member_id CHAR (10) NOT NULL
      REFERENCES Membership (member_id)
    );

    I had to make an assumption since you didn't bother with any of the proper constraints that the policyholder is also in the membership. Was I wrong?

    Since I'm very old and have been doing this for a few decades, I really enjoyed seeing you put commas at the start of each line in the declaration. We used to do that with punchcards in the 1960s, so we could rearrange the physical deck of cards. Nobody has done that since the late 1960s, because it messes up legibility and formatting should be done with a pretty printer piece of software instead.

    The ANSI/ISO standard is cast () and not the old Sybase proprietary convert (). You might want to start writing portable code.  The rest of your posting makes no sense; we have no ZIP Code column in the four DDL you tried to post, we have no coverage date,  etc.

    What do you think you have done right? But more important to me, why do you think it was right? I want my last book on SQL to be a collection of absolutely horrible bad code. But people do not usually make random mistakes. They have a bad mental model of what they're doing, and I want to know what that model is.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by cloudsInSky Tuesday, April 28, 2020 11:31 PM
    Tuesday, April 28, 2020 9:42 PM
  • >> Policy_Holder_MemberID References Membership (Member_ID). Does that mean we have both PK And FK in one table? <<

    Yes. Technically, we refer to referenced and referencing tables. They can be the same table. In standard ANSI/ISO SQL, you can defer constraints at the time the table is created. In SQL Server, you have to explicitly disable a constraint, do your insertions, updates, etc., then reenable it.

    >> What does CHECK(sex_code IN ('0', '1', '2', '9') want to say? 

    This is called a check constraint and you need to just read about it. It is a part of the DDL that does not allow you to insert data into a table or column that violates the constraint.. Besides guaranteeing data integrity, these constraints also are passed to the optimizer to improve the performance of your code.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by cloudsInSky Saturday, May 2, 2020 1:22 AM
    Friday, May 1, 2020 8:46 PM
  • Hello Ronen,

    Per what you say:

    the column is type Date then it try to convert the text into date. 

    I want to convert the date into a text.

    SELECT Convert(nVarchar(12), 2020-01-01, 101) From MyTable

    When I don't place the date into a single quotations, SQL starts calculation on that even though its data type is defined as date when I created the table.

    Just by experience and from what I read from your link, I need to put the dates into a quotes.

    This is what I come up with. 

    Still, I have this question:

    Why the conversion of date to text is implicit? If I don't use convert function, I can't convert the date field into a text.

    Also, then this question comes? How to insert a date value as a date into a table.

    Respectfully,

    CloudsInSky


    CloudsInSky

    HI,

    It seems like you do the same mistake. THIS IS NOT A STRING: 2020-01-01, It is a math operation on integers!

    Try to execute the following query:

    select 2020-01-01

    The result is 2018 since you have 2020-1-1 = 2018

    In order to use a string it should be inside a quotation marks. THIS IS A STRING: '2020-01-01'

    >> When I don't place the date into a single quotations, SQL starts calculation on that

    This is exactly what I told you in previous message!

    As I said "the value: 01/01/2020 IS NOT A DATE" since there are no quotations then this is not a STRING and it is not a a date. It is a math on integers

    >> Just by experience and from what I read from your link, I need to put the dates into a quotes.

    Yes, if this is a string then it should be inside quotes😀

    If you want the server to convert a string then it should first be a string

    The server can convert implicitly and explicitly from different types into different types, but in your case if you want to use a format like '2020-01-01' then this is a string and therefore it should be inside quotes

    >> Why the conversion of date to text is implicit?

    You do not have a date. You write queries with text and not with objects. If you use parameters and objects for example using .Net, instead of something like '2020-01-01', then you can use Date which mean that the client will send a binary data and not something like '2020-01-01'.

    This '2020-01-01' is string!

    If you want more (VERY ADVANCED) information about what actually is used when you use for example data type DATETIME2 then  you can check the following post:
    http://ariely.info/Blog/tabid/83/EntryId/162/Examine-how-DateTime2-type-stored-in-the-data-file.aspx

    You need to understand the different between text (string) and data types like DATE. Not all data is readable! What you read a date when you use SSMS for example and get result of a query is NOT A DATE but a string which represent the data of the DATE which is a set of zero and one (binary data). The SSMS convert the binary data of the DATE into a string in order to present it. Same as the SQL Server try to convert the string into DATE if needed.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    • Marked as answer by cloudsInSky Thursday, April 30, 2020 2:48 AM
    Wednesday, April 29, 2020 5:48 AM
  • Hello Ronen,

    If we write:

    convert(nvarchar(20), BirthDate) 

    doesn't make sense, because that BirthDate (Data type is date) is stored as a string and we don't want to convert string to string.

    What do you think?

    Respectfully,

    CloudsInSky


    CloudsInSky

    hi,

    >> because that BirthDate (Data type is date) is stored as a string

    You are TOTALLY wrong! and this is probably what lead you to these mistakes. Seems like you do not understand what is a "type of data" probably and you think of the data as you see it.

    Check the post I gave you about DATETIME2 which is a great example of how SQL Server actually stores data type DATETIME2. 

    You should remember that in the disk we do not have strings but only zero and one. Using set of 0/1 we represent the value according to the type.

    the value 1 when it is INT is store totally different from the value 1 when it is NVARCHAR and it is totally different t from the way 1 is stored as VARCHAR and so on...

    How exactly the different types are stored is more advanced topic of what you probably deal with in your daily work but you should understand the basic which is that data type is not just a name but a different type which is stored according to the type and NOT like any other type of data.

    >> What do you think?

    It is not related to what we think since these are fact and not an open discussion on opinion. In fact I have multiple live demonstration where I teach about SQL Server internals in event and conferences around the world.

    The one I like the most is this one: 
    https://www.sqlsaturday.com/623/Sessions/Details.aspx?sid=63290

    This lecture is a live demo, where I create new database -> I create new table -> I ask the audience to give me some information to fill in the table (this way they know that this is not a fake but real live demo) -> I detach the database from the server -> I open the data file using notepad ++ hexadecimal editor -> people can see the real content of the file which is a set of many many 0 and 1... -> and then I show them then I can read all the data and I teach how we can translate the zero and one into something we can understand. I show how I can read all the information which we filled in the table and how it is stored in the disk according to it's DATA TYPE

    Sometimes I name this lecture: WHO NEED SQL SERVER IF YOU HAVE NOTEPAD AND INTERNALS KNOWLEDGE 


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Wednesday, April 29, 2020 6:50 PM
    • Marked as answer by cloudsInSky Thursday, April 30, 2020 2:49 AM
    Wednesday, April 29, 2020 6:29 PM

All replies

  • Hi cloudsInSky, 

    Your code has some problems with syntax usage. Please  check following script . 

    CREATE TABLE tbl_All_Members
    (
     MemberID int PRIMARY KEY NOT NULL
    ,FirstName nVarchar (50) NOT NULL
    ,LastName nVarchar (100) NOT NULL
    ,DateOFBirth Date
    ,DateOFBirthASText nVarchar (20)
    ,AGE int
    ,Gender nVarChar(2)
    ,PolicyHolderMemberID nVarchar(200) NOT Null
    )
    
    declare @test table (
     MemberID int PRIMARY KEY NOT NULL
    ,FirstName nVarchar (50) NOT NULL
    ,LastName nVarchar (100) NOT NULL
    ,DateOFBirth Date
    ,ZipCode int
    )
    insert into @test
     VALUES(1, 'Mary', 'Jackson', '01/01/1988', 92677);
    
    insert into tbl_All_Members 
    select  MemberID
     ,FirstName
     ,LastName
     ,DateOFBirth
     ,Convert(Nvarchar(12), DateOfBirth) AS DateOFBirthASText
     ,DateDiff(Y, DateofBirth, GetDate()) AS AGE
     ,(CASE
        When zipcode % 2 = 1 Then 'M'
        ELSE 'F'
      END) AS GENDER
     ,ZipCode 
    from @test
    
    select* from tbl_All_Members
    /*
    MemberID    FirstName  LastName DateOFBirth DateOFBirthASText    AGE         Gender PolicyHolderMemberID
    ----------- ---------- -------------------- -------------------- ----------- ------ --------------------------
    1           Mary       Jackson  1988-01-01  1988-01-01           11806       M      92677
    
    */

    Best Regards,

    Rachel 



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by cloudsInSky Tuesday, April 28, 2020 7:55 PM
    Tuesday, April 28, 2020 2:26 AM
  • Hello Rachel_Wang,

    I added these three columns that I didn't include them in the code originally.

    ALTER Table tbl_All_Members ADD CoverageEffDt Date;
    ALTER TAble tbl_All_Members ADD FullName nVarChar(150);
    ALTER TABLE tbl_All_Members ADD ZipCode int;

    I can't see what you have done.

    Regards,

    CloudsInSky


    CloudsInSky

    Tuesday, April 28, 2020 4:23 AM
  • Good day CloudsInSky,

    You are using explicit convert of text to date in your INSERT query which lead to this issue. This is HIGHLY not recommended and considered as poor approach. The main issue in this approach is that it is worse then getting error since it might work and insert wrong data to the table!

    Your query uses the text: '01/01/1988'

    THIS IS A TEXT AND NOT A DATE!

    During the execution the server need to guess what the hell you mean to do and since the column is type Date then it try to convert the text into date implicitly behind the scene and this is not so simple since you are using a nun-clear format.

    You must read the following post about the different between "Date displaying format, vs Date storing format" !
    You must use explicit CONVERT and use the right STYLE parameter in order to tell the server what is the format of the input text.

    As explain in the post, Implicit conversion of ambiguous date formats are interpreted according to the language of the connection. Always keep and follow the rules in the post's conclusions


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, April 28, 2020 5:36 AM
  • Hi,

    Please check this one . 

    IF OBJECT_ID('tbl_All_Members') IS NOT NULL drop table  tbl_All_Members   
    go 
    
    CREATE TABLE tbl_All_Members
    (
     MemberID int PRIMARY KEY NOT NULL
    ,FirstName nVarchar (50) NOT NULL
    ,LastName nVarchar (100) NOT NULL
    ,DateOFBirth Date
    ,DateOFBirthASText nVarchar (20)
    ,AGE int
    ,Gender nVarChar(2)
    ,PolicyHolderMemberID nVarchar(200) NOT Null
    )
    
    ALTER Table tbl_All_Members ADD CoverageEffDt Date;
    ALTER TAble tbl_All_Members ADD FullName nVarChar(150);
    ALTER TABLE tbl_All_Members ADD ZipCode int;
    
    
    declare @test table (
     MemberID int PRIMARY KEY NOT NULL
    ,FirstName nVarchar (50) NOT NULL
    ,LastName nVarchar (100) NOT NULL
    ,DateOFBirth Date
    ,ZipCode int
    )
    insert into @test
     VALUES(1, 'Mary', 'Jackson', '01/01/1988', 92677);
    
    insert into tbl_All_Members 
    select  MemberID
     ,FirstName
     ,LastName
     ,DateOFBirth
     ,Convert(Nvarchar(12), DateOfBirth) AS DateOFBirthASText
     ,DateDiff(Y, DateofBirth, GetDate()) AS AGE
     ,(CASE
        When zipcode % 2 = 1 Then 'M'
        ELSE 'F'
      END) AS GENDER,
      Concat('01', ' ', MemberID) AS PolicyHolderMmeberID
     ,GetDate() AS CoverageEffDt
     ,Concat(FirstName, ' ' , LastName) AS FullName
     ,ZipCode 
    from @test
    
    select* from tbl_All_Members
    /*
    MemberID    FirstName      LastName    DateOFBirth DateOFBirthASText    AGE         Gender PolicyHolderMemberID   CoverageEffDt FullName       ZipCode
    ----------- -------------- ----------- ----------- -------------------- ----------- ------ ---------------------- ------------- -------------- -----------
    1           Mary           Jackson     1988-01-01  1988-01-01           11806       M      01 1                   2020-04-28    Mary Jackson   92677
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by cloudsInSky Tuesday, April 28, 2020 7:56 PM
    Tuesday, April 28, 2020 5:43 AM
  • Hello Ronen Ariely,

    I wrote this one:

     Create table MyTable
     (DateOfBirth Date)
     INSERT into MyTable (DateOfBirth)
     Values (01/01/2020)

    It gave me an error message:

    Msg 206, Level 16, State 2, Line 50
    Operand type clash: int is incompatible with date

    I had to put 01/01/2020 in single quotes, it worked. Let me know how to go around this. 


    CloudsInSky

    Tuesday, April 28, 2020 5:15 PM
  • Hello Rachel,

    Thanks for your response.

    Can we take another approach and stay away from variable table?

    Can we simply use this syntax?

    INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
    VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

    Respectfully,

    CloudsInSky 


    CloudsInSky


    • Edited by cloudsInSky Tuesday, April 28, 2020 5:24 PM
    Tuesday, April 28, 2020 5:22 PM
  • Hello Ronen Ariely,

    I wrote this one:

     Create table MyTable
     (DateOfBirth Date)
     INSERT into MyTable (DateOfBirth)
     Values (01/01/2020)

    It gave me an error message:

    Msg 206, Level 16, State 2, Line 50
    Operand type clash: int is incompatible with date

    I had to put 01/01/2020 in single quotes, it worked. Let me know how to go around this. 


    CloudsInSky

    Same issue with different data type

    the value: 01/01/2020
    IS NOT A DATE

    The server tries to convert it and this time it only "see" the value as a math calculation, which is 01 divided by 01 divided by 2020 (rounded since it is all INT). The server parse Engine think that thgis is INT and raise the error

    I gave you the full solution: check the link to the post I added above. It is all there including the "OK" solutions" and the "Best Practice" solution.

    Please check the post from start to end (only 15 minutes max including testing), and if it is still not clear I will clarify here (and maybe edit the post to make it better clear)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Tuesday, April 28, 2020 5:51 PM
    Tuesday, April 28, 2020 5:48 PM
  • To answer your question, the INSERT statement syntax you posted is incorrect. 

    Please see:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15

    The correct syntax is "INSERT INTO TABLENAME (columnname1, columnname2, ....)".  You cannot specify conversions in the column list.

    You also cannot do functions like CONVERT in the "VALUES" section, without using SELECT. 

    So the correct syntax is:

    insert into tbl_All_Members (
    	MemberID
    	,FirstName
    	,LastName
    	,DateOFBirth
    	,DateOFBirthASText
    	,AGE
    	,Gender
    	,PolicyHolderMemberID
    	)
    select 
    	1 
     ,'Mary'
     ,'Jackson'
     ,'01/01/1988'
     ,Convert(Nvarchar(12), '01/01/1988')
     ,DateDiff(Y, '01/01/1988', GetDate())
     ,(CASE
        When 92677 % 2 = 1 Then 'M'
        ELSE 'F'
      END) AS GENDER
     ,92677


    • Edited by Tom Phillips Tuesday, April 28, 2020 6:16 PM
    • Marked as answer by cloudsInSky Tuesday, April 28, 2020 7:51 PM
    Tuesday, April 28, 2020 6:15 PM
  • Hello Ronen,

    Per what you say:

    the column is type Date then it try to convert the text into date. 

    I want to convert the date into a text.

    SELECT Convert(nVarchar(12), 2020-01-01, 101) From MyTable

    When I don't place the date into a single quotations, SQL starts calculation on that even though its data type is defined as date when I created the table.

    Just by experience and from what I read from your link, I need to put the dates into a quotes.

    This is what I come up with. 

    Still, I have this question:

    Why the conversion of date to text is implicit? If I don't use convert function, I can't convert the date field into a text.

    Also, then this question comes? How to insert a date value as a date into a table.

    Respectfully,

    CloudsInSky


    CloudsInSky


    • Edited by cloudsInSky Tuesday, April 28, 2020 8:25 PM
    Tuesday, April 28, 2020 8:20 PM
  • Everything you've posted is wrong. The prefix "TBL_"  is a design flaw so bad that it has a name; it is called a "tibble". But since member_member_firstname table models a complete set up some kind of entity you made this even worse by adding that "All_" prefix. A table name should be a collective or plural noun since it does model a set.

    Next, member ID is based on a nominal scale since it's an identifier. But by definition, an identifier cannot be a numeric because you do know math on it. I also have to ask how many people you know whose names are over 150 characters long in Unicode.  Really? The international postal Union convention is that a line and address should be 35 or fewer characters. This is based on a 3.5 inch pressure label that was in industry-standard.  

    The whole goal of databases, not just SQL, was to reduce in redundancy data that could be computed from existing columns is never materialized in a good SQL schema. Please think about it for a minute; you can compute the results faster than you can read them off the disk, as well as saving disk space.

    If you learned ISO standards, you would know there is no such thing as gender; it's actually called the sex code and is a single character. But it does not have to be in Mandarin Chinese. 

    CREATE TABLE Membership
    (member_id CHAR(10) NOT NULL PRIMARY KEY,
     member_firstname NVARCHAR  (35) NOT NULL,
     member_lasttname  NVARCHAR  (35) NOT NULL,
     birth_date DATE,
     sex_code CHAR (1) NOT NULL DEFAULT '0'
         CHECK (sex_code IN ('0', '1', '2', '9'),
     policy_holder_member_id CHAR (10) NOT NULL
      REFERENCES Membership (member_id)
    );

    I had to make an assumption since you didn't bother with any of the proper constraints that the policyholder is also in the membership. Was I wrong?

    Since I'm very old and have been doing this for a few decades, I really enjoyed seeing you put commas at the start of each line in the declaration. We used to do that with punchcards in the 1960s, so we could rearrange the physical deck of cards. Nobody has done that since the late 1960s, because it messes up legibility and formatting should be done with a pretty printer piece of software instead.

    The ANSI/ISO standard is cast () and not the old Sybase proprietary convert (). You might want to start writing portable code.  The rest of your posting makes no sense; we have no ZIP Code column in the four DDL you tried to post, we have no coverage date,  etc.

    What do you think you have done right? But more important to me, why do you think it was right? I want my last book on SQL to be a collection of absolutely horrible bad code. But people do not usually make random mistakes. They have a bad mental model of what they're doing, and I want to know what that model is.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by cloudsInSky Tuesday, April 28, 2020 11:31 PM
    Tuesday, April 28, 2020 9:42 PM
  • Hello,

    Thanks for all the feedback you gave and the points are very valuable. Your feedback is really constructive. I am just a beginner that wants to learn and make hands dirty.

    I have learned a lot from all that is written in this thread.

    Respectfully,

    CloudsInSky



    CloudsInSky

    Tuesday, April 28, 2020 11:21 PM
  • Hello Ronen,

    Per what you say:

    the column is type Date then it try to convert the text into date. 

    I want to convert the date into a text.

    SELECT Convert(nVarchar(12), 2020-01-01, 101) From MyTable

    When I don't place the date into a single quotations, SQL starts calculation on that even though its data type is defined as date when I created the table.

    Just by experience and from what I read from your link, I need to put the dates into a quotes.

    This is what I come up with. 

    Still, I have this question:

    Why the conversion of date to text is implicit? If I don't use convert function, I can't convert the date field into a text.

    Also, then this question comes? How to insert a date value as a date into a table.

    Respectfully,

    CloudsInSky


    CloudsInSky

    HI,

    It seems like you do the same mistake. THIS IS NOT A STRING: 2020-01-01, It is a math operation on integers!

    Try to execute the following query:

    select 2020-01-01

    The result is 2018 since you have 2020-1-1 = 2018

    In order to use a string it should be inside a quotation marks. THIS IS A STRING: '2020-01-01'

    >> When I don't place the date into a single quotations, SQL starts calculation on that

    This is exactly what I told you in previous message!

    As I said "the value: 01/01/2020 IS NOT A DATE" since there are no quotations then this is not a STRING and it is not a a date. It is a math on integers

    >> Just by experience and from what I read from your link, I need to put the dates into a quotes.

    Yes, if this is a string then it should be inside quotes😀

    If you want the server to convert a string then it should first be a string

    The server can convert implicitly and explicitly from different types into different types, but in your case if you want to use a format like '2020-01-01' then this is a string and therefore it should be inside quotes

    >> Why the conversion of date to text is implicit?

    You do not have a date. You write queries with text and not with objects. If you use parameters and objects for example using .Net, instead of something like '2020-01-01', then you can use Date which mean that the client will send a binary data and not something like '2020-01-01'.

    This '2020-01-01' is string!

    If you want more (VERY ADVANCED) information about what actually is used when you use for example data type DATETIME2 then  you can check the following post:
    http://ariely.info/Blog/tabid/83/EntryId/162/Examine-how-DateTime2-type-stored-in-the-data-file.aspx

    You need to understand the different between text (string) and data types like DATE. Not all data is readable! What you read a date when you use SSMS for example and get result of a query is NOT A DATE but a string which represent the data of the DATE which is a set of zero and one (binary data). The SSMS convert the binary data of the DATE into a string in order to present it. Same as the SQL Server try to convert the string into DATE if needed.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    • Marked as answer by cloudsInSky Thursday, April 30, 2020 2:48 AM
    Wednesday, April 29, 2020 5:48 AM
  • Hello Ronen,

    If we write:

    convert(nvarchar(20), BirthDate) 

    doesn't make sense, because that BirthDate (Data type is date) is stored as a string and we don't want to convert string to string.

    What do you think?

    Respectfully,

    CloudsInSky


    CloudsInSky

    Wednesday, April 29, 2020 5:43 PM
  • Hello Ronen,

    If we write:

    convert(nvarchar(20), BirthDate) 

    doesn't make sense, because that BirthDate (Data type is date) is stored as a string and we don't want to convert string to string.

    What do you think?

    Respectfully,

    CloudsInSky


    CloudsInSky

    hi,

    >> because that BirthDate (Data type is date) is stored as a string

    You are TOTALLY wrong! and this is probably what lead you to these mistakes. Seems like you do not understand what is a "type of data" probably and you think of the data as you see it.

    Check the post I gave you about DATETIME2 which is a great example of how SQL Server actually stores data type DATETIME2. 

    You should remember that in the disk we do not have strings but only zero and one. Using set of 0/1 we represent the value according to the type.

    the value 1 when it is INT is store totally different from the value 1 when it is NVARCHAR and it is totally different t from the way 1 is stored as VARCHAR and so on...

    How exactly the different types are stored is more advanced topic of what you probably deal with in your daily work but you should understand the basic which is that data type is not just a name but a different type which is stored according to the type and NOT like any other type of data.

    >> What do you think?

    It is not related to what we think since these are fact and not an open discussion on opinion. In fact I have multiple live demonstration where I teach about SQL Server internals in event and conferences around the world.

    The one I like the most is this one: 
    https://www.sqlsaturday.com/623/Sessions/Details.aspx?sid=63290

    This lecture is a live demo, where I create new database -> I create new table -> I ask the audience to give me some information to fill in the table (this way they know that this is not a fake but real live demo) -> I detach the database from the server -> I open the data file using notepad ++ hexadecimal editor -> people can see the real content of the file which is a set of many many 0 and 1... -> and then I show them then I can read all the data and I teach how we can translate the zero and one into something we can understand. I show how I can read all the information which we filled in the table and how it is stored in the disk according to it's DATA TYPE

    Sometimes I name this lecture: WHO NEED SQL SERVER IF YOU HAVE NOTEPAD AND INTERNALS KNOWLEDGE 


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Wednesday, April 29, 2020 6:50 PM
    • Marked as answer by cloudsInSky Thursday, April 30, 2020 2:49 AM
    Wednesday, April 29, 2020 6:29 PM
  • If we write:

    convert(nvarchar(20), BirthDate) 

    doesn't make sense, because that BirthDate (Data type is date) is stored as a string and we don't want to convert string to string.

    If the data type of BirthDate is date, it is not string. Yes, you see a string when you select the value, but it is a just a textual representation. What it is actually stored as I don't know, but I would guess it is the number of days since Jan 1at, 1AD.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, April 29, 2020 9:50 PM
  • Erland,

    Yes, I agree with you. It is represented as a string but actually its data type is date.

    Respectfully,

    CloudsInSky


    CloudsInSky

    Thursday, April 30, 2020 2:42 AM
  • Hello CELKO,

    In this syntax of create table:

    CREATE TABLE Membership
    (member_id CHAR(10) NOT NULL PRIMARY KEY,
     member_firstname NVARCHAR  (35) NOT NULL,
     member_lasttname  NVARCHAR  (35) NOT NULL,
     birth_date DATE,
     sex_code CHAR (1) NOT NULL DEFAULT '0'
         CHECK (sex_code IN ('0', '1', '2', '9'),
     policy_holder_member_id CHAR (10) NOT NULL
      REFERENCES Membership (member_id)
    );

    Policy_Holder_MemberID References Membership (Member_ID). Does that mean we have both PK And FK in one table?

    What does CHECK(sex_code In('0', '1', '2', '9') want to say? Can we put it in a syntax that would say if it is ('0', '1', '2', '9') then it is a m else f.

    Respectfully,

    CloudsInSky


    CloudsInSky

    Thursday, April 30, 2020 3:12 AM
  • Yes, I agree with you. It is represented as a string but actually its data type is date.

    No, it is not represented as a string. It is presented as a string, but that's not the same thing as the internal representation.

    One way to get an idea of the internal represenation of a value is to cast it to binary, altought the fidelity may not be 100%. (For that you would have to use things like DBCC PAGE, but that is a lot more advanced.) Look at this:

    SELECT datediff(DAY, convert(date, '00010101'), '20200430'),        convert(varbinary(4), datediff(DAY, convert(date, '00010101'), '20200430')),
           convert(varbinary(4), convert(date, '20200430'))

    The result is:

    737544     0x000B4108   0x08410B

    The bytes for the date value are the same as for the bytes for the integer value of the number of days since Jan 1st, 1AD. For some reason, the order of the bytes are not the same, but that could be related to how the conversion to binary is done.

    Going back to how the date value is presented, you should keep in mind that this is something that happens in Management Studio. When SQL Server sends the date value to SSMS, it sends the bytes 08, 41 and 0B as above, not the bytes for the string 2020-04-30.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, April 30, 2020 3:53 PM
  • What does CHECK(sex_code In('0', '1', '2', '9') want to say? Can we put it in a syntax that would say if it is('0', '1', '2', '9') then it is a m else f.

    CHECK(sex_code In('0', '1', '2', '9') means that the permitted values fpr sex_code are '0', '1', '2' and '9'. To find out what these values actually means, you will need to read the documentation for the databsae.

    Something like "m else f" does not seem to be applicable here. If am to make a guess two of the values certainly the traditional male and female. One value is probably the third gender which is legally recognised in some countries (and which exists biologically in all jurisdictions). The fourth value could be "don't want to tell".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, April 30, 2020 9:40 PM
  • >> Policy_Holder_MemberID References Membership (Member_ID). Does that mean we have both PK And FK in one table? <<

    Yes. Technically, we refer to referenced and referencing tables. They can be the same table. In standard ANSI/ISO SQL, you can defer constraints at the time the table is created. In SQL Server, you have to explicitly disable a constraint, do your insertions, updates, etc., then reenable it.

    >> What does CHECK(sex_code IN ('0', '1', '2', '9') want to say? 

    This is called a check constraint and you need to just read about it. It is a part of the DDL that does not allow you to insert data into a table or column that violates the constraint.. Besides guaranteeing data integrity, these constraints also are passed to the optimizer to improve the performance of your code.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by cloudsInSky Saturday, May 2, 2020 1:22 AM
    Friday, May 1, 2020 8:46 PM
  • Hello CELKO,

    You used char for MemberID. Why don't you use nVarChar for MemberID?

    Regards,

    CloudsInSky


    CloudsInSky

    Saturday, May 2, 2020 1:39 AM