Isabell
asked on
Creating a table with formatted data
How do I create the table has the following date format in ms sql server
child_Id | name | birthdate
1 | sam | 12/24/2013
2 | bob | 1/3/2016
create table children (
child_Id int not null identity primary key,
name varchar(30),
birthdate date // I think that this is wrong
)
insert into children(name,birthdate)
values ('sam','12/24/2013')
even if we do
insert into children(name,birthdate)
values('sam',convert(varch
it still doesn't work.
child_Id | name | birthdate
1 | sam | 12/24/2013
2 | bob | 1/3/2016
create table children (
child_Id int not null identity primary key,
name varchar(30),
birthdate date // I think that this is wrong
)
insert into children(name,birthdate)
values ('sam','12/24/2013')
even if we do
insert into children(name,birthdate)
values('sam',convert(varch
it still doesn't work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Éric Moreau
you shouldn't care about the format in which the database stores the date, it is the job of the presentation layer (Application and/or reporting engine) to format the date in the required format.
ASKER
Thanks guys.
But let's say that I used this as Scott Pletcher recommanded.
create table children (
child_Id int not null identity primary key,
name varchar(30),
birthdate date // I think that this is wrong
)
insert into children(name,birthdate)
values ('sam','20131224')
insert into children(name,birthdate)
values ('bob', '20160103')
Now what I want is if do
select * children
I want to see
child_Id name birthdate
----------- --------------------------
1 sam 12/24/2013
2 bob 01/03/2016
Currently, it prints out like
child_Id name birthdate
----------- --------------------------
1 sam 2013-12-24
2 bob 2016-01-03
But let's say that I used this as Scott Pletcher recommanded.
create table children (
child_Id int not null identity primary key,
name varchar(30),
birthdate date // I think that this is wrong
)
insert into children(name,birthdate)
values ('sam','20131224')
insert into children(name,birthdate)
values ('bob', '20160103')
Now what I want is if do
select * children
I want to see
child_Id name birthdate
----------- --------------------------
1 sam 12/24/2013
2 bob 01/03/2016
Currently, it prints out like
child_Id name birthdate
----------- --------------------------
1 sam 2013-12-24
2 bob 2016-01-03
SELECT child_id, name, CONVERT(varchar(10), birthdate, 101) AS birthdate
FROM dbo.children
The date is stored internally as an integer, with no editing chars of any kind. SQL has a default display format, which is why you see yyyy-mm-dd. But you can use CONVERT or other functions to get any display format you want. Note that the entry format has nothing to do with the display format.
FROM dbo.children
The date is stored internally as an integer, with no editing chars of any kind. SQL has a default display format, which is why you see yyyy-mm-dd. But you can use CONVERT or other functions to get any display format you want. Note that the entry format has nothing to do with the display format.
ASKER
Thank you all~