Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
satheesh
Posting Yak Master
152 Posts |
Posted - 2011-02-18 : 05:19:38
|
Hi all,Urgent help any help would be highly appreiciatedI 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.Thanks.. |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-02-18 : 05:32:45
|
UPDATE dbo.Table1 SET Col1 = DATEDIFF(DAY, 0, Col1) -- reset timeinformation to 00:00:00.000If 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" |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-18 : 05:33:16
|
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 . CheersMIK |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2011-02-18 : 05:53:17
|
Date Datatype only available in 2008 version.-------------------------/R.. |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-02-18 : 22:21:34
|
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: http://www.catb.org/~esr/faqs/smart-questions.html>> 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;.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|