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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

fawzinn
Starting Member

6 Posts

Posted - 2012-10-01 : 04:29:08
how i can make it , give me the statement please
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

fawzinn
Starting Member

6 Posts

Posted - 2012-10-01 : 10:15:30
Up
Go to Top of Page

fawzinn
Starting Member

6 Posts

Posted - 2012-10-02 : 06:16:05
no one can help me ??
Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -