Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Urgent Help Plz..
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

United Kingdom
152 Posts

Posted - 02/18/2011 :  05:19:38  Show Profile  Reply with Quote
Hi all,

Urgent help any help would be highly appreiciated

I have a date field in a table (format:2010-03-06 15:47:36.747)
1.I need to select only date when selecting that field ie(select cststartdate from cst)i want the output be '2010-03-06'

2.Also i want to know how to delete this second (15:47:36.747) in that field for all datas.


Patron Saint of Lost Yaks

30421 Posts

Posted - 02/18/2011 :  05:32:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE dbo.Table1 SET Col1 = DATEDIFF(DAY, 0, Col1) -- reset timeinformation to 00:00:00.000

If you are using SQL Server 2008 or later, do this instead.
ALTER TABLE dbo.Table1 ALTER COLUMN Col1 DATE [NOT NULL] -- change datatype to DATE. Be sure null (or not null) is appropriate.

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Flowing Fount of Yak Knowledge

1054 Posts

Posted - 02/18/2011 :  05:33:16  Show Profile  Reply with Quote
1) Use this for the display in the required format of the desired column .. convert(date,cststartdate,105)

2) In that case you should have created a field with DataType as Date .

Go to Top of Page

Aged Yak Warrior

595 Posts

Posted - 02/18/2011 :  05:53:17  Show Profile  Send rajdaksha a Yahoo! Message  Reply with Quote
Date Datatype only available in 2008 version.


Go to Top of Page

Esteemed SQL Purist

547 Posts

Posted - 02/18/2011 :  22:21:34  Show Profile  Visit jcelko's Homepage  Reply with Quote

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at:

>> I have a date field [sic} in a table (format:2010-03-06 15:47:36.747) <<

Columns are not fields and they have no format. You have no idea how SQL works!

>> I need to select only date when selecting that field [sic], ie(SELECT cst_start_date FROM CST). I want the output be '2010-03-06' <<

CAST ( cst_start_date AS DATE )

>>.Also I want to know how to delete this second (15:47:36.747) in that field [sic]for all dates.<<

Do an ALTER TABLE and get rid of the DATETIME column;.

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000