Convert Datetime to short date

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Convert Datetime to short date

Author

Topic

fawzinn
Starting Member

6 Posts

Posted - 2012-10-01 : 02:42:15

Hello
i have date&time field and i want to add a new column in the view to convert the date&time to short date with column type(Date) bec i need it in query and i tried the convert statement but it's convert it as varchar, can any one help me please ?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-10-01 : 03:17:37

I assume that you are using SQL Server 2000, here data and time cannot be stored seperately.
You have to use convert/datepart function to seperate it.

Data and time datatypes are introduced in Sql server 2008 which may resolve your issue.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

fawzinn
Starting Member

6 Posts

Posted - 2012-10-01 : 03:26:27

no i'm using sql server 2008, i tried a function access called (DateValue) to get the date from datetime with date type but i didn't find a function like it in sql server

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-10-01 : 04:14:54

Then you can type cast datetime to time/date datatype.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

fawzinn
Starting Member

6 Posts

Posted - 2012-10-01 : 04:29:08

how i can make it , give me the statement please

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-10-01 : 04:39:31

CONVERT(DATE,GETDATE())

Note it will work 2008 or higher.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-10-01 : 04:41:27

Please have an eye on this for more understanding..

http://beyondrelational.com/modules/2/blogs/70/Posts/17608/different-ways-to-remove-time-part-from-datetime-values-faster-methods.aspx

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

fawzinn
Starting Member

6 Posts

Posted - 2012-10-01 : 05:13:14

thanks Mr.Senthil , but it try many function but still i didn't take the solution , bec in the sql i can't use any function contains type (date) the sql server accept only datetime or i should convert it to text and this not a solution for me bec i can't use the text to make a query on period from to , and date time contains time that disallow me to make a query on short date

fawzinn
Starting Member

6 Posts

Posted - 2012-10-01 : 10:15:30

fawzinn
Starting Member

6 Posts

Posted - 2012-10-02 : 06:16:05

no one can help me ??

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-02 : 07:33:42

Take a look at the link that Senthil posted - if anything, it has more information than you need. Here is an example:

DECLARE @date DATETIME = '2012-10-02 07:30:30.860';

SELECT CAST(@date AS DATE);
SELECT DATEADD(dd,DATEDIFF(dd,0,@date),0);

In each of the select statements, it removes the time portion. In the first case the result is of type DATE (which is a data type available in SQL 2008 and later). In the second, the result is still of type datetime, but the time portion has been stripped off.

If neither of this is what you are looking for, can you post an example of your input data and the desired output?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources