Need a simple sql sript

What is the data format you want to end up with?

Try

select substring(birthdate,1,19),
...

Select allOpen in new window

:)

if the table has a field called [birthdate] and that field is defined as 'datetime'*
then the 00:00:00 can only be "suppressed" (not removed)

date/time information is NOT stored in a readable format (it is stored in a binary manner, actually sets of numbers)

so to DISPLAY a birthdate you can choose from many options, e.g.

select
  convert(varchar(10),[birthdate],121) as bday_format1
from YourTable

if you are using sql server 2012 there are other date formatting options

*
datetime, datetime2, smalldatetime all contain a time element

one other option qould be to alter that field from datatime to just date - which will permanently remove the time element

I'll just go with a date type to give you options. And I'll assume the the column is a datetime:

select convert( date, BirthDate, 101 ) as convertedBirthDate from student;

Select allOpen in new window

here's a link for ways to change the display of date/time information
(assuming that field is a datetime or similar)

or a longer list of options: http://www.sql-server-helper.com/tips/date-formats.aspx

I am using sql 2012 I am just trying to update the column from the date time format to date. It is currently yyyy-mm-dd 00:00:00.000 I would like it mm-dd-yyyy. I am trying to use sql to manipulate data and I am pretty much clueless. So if you could spell out for me I would appreciate it. The table name is student_1213 the column name is BIRTH DATE so i tried this:'

select convert( date, [Birth Date], 101 ) as converted[Birth Date] from student_1213

and it would not execute

I am looking through the table you gave me portletpaul but I am unsure of the syntax

thanks

Try

select CONVERT(varchar,cast(substring(birthdate,1,10) as datetime),110),
...

Select allOpen in new window

Try this:

select convert( date, [Birth Date], 112 ) as [converted Birth Date] from student_1213;

Select allOpen in new window

Edit: edited the conversion style to reflect the format you mentioned...

didnthaveaname
The script gave me the solution. Now if I want to update the table do I just need to change select to update

Thanks

Assuming you want to update every instance of that birthdate it would look like this:

update student_1213
set [Birth Date] = convert( date, [Birth Date], 112 );

Select allOpen in new window

Edit: fixed verbosity in update statement

Try

update tab1
set birthdate=CONVERT(varchar,cast(substring(birthdate,1,10) as datetime),110)
...

Select allOpen in new window

the script runs successfully (so it says) but the zeros are still at the end of the dat in the Birth Date column

HuaMinChen
I receive the following error when I run your script:
Msg 8116, Level 16, State 1, Line 1
Argument data type datetime is invalid for argument 1 of substring function.

thanks for the grade.

as you are inexperienced in SQL, may I suggest this page as a good one for date/times:
The ultimate guide to the datetime datatypes

it is important that you understand date/times are not stored the way you see them - and there are really good reasons for this. Correct sorting of dates for example would not be possible or date/time arithmetic would also not be possible if they were stored they way we see them. providing date formats in English/French/German/Korean/Whatever would also not be possible. Internally SQL Server actually stores datetime as 2 integers, and from those a whole raft of capabilities are possible.

welcome to the world of SQL. Cheers, Paul