SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Convert Datetime to short date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fawzinn
Starting Member

Saudi Arabia
6 Posts

Posted - 10/01/2012 :  02:42:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 10/01/2012 :  03:17:37  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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

Saudi Arabia
6 Posts

Posted - 10/01/2012 :  03:26:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 10/01/2012 :  04:14:54  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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

Saudi Arabia
6 Posts

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

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 10/01/2012 :  04:39:31  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 10/01/2012 :  04:41:27  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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

Saudi Arabia
6 Posts

Posted - 10/01/2012 :  05:13:14  Show Profile  Reply with Quote
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

Saudi Arabia
6 Posts

Posted - 10/01/2012 :  10:15:30  Show Profile  Reply with Quote
Up
Go to Top of Page

fawzinn
Starting Member

Saudi Arabia
6 Posts

Posted - 10/02/2012 :  06:16:05  Show Profile  Reply with Quote
no one can help me ??
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/02/2012 :  07:33:42  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New 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.09 seconds. Powered By: Snitz Forums 2000