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
 General SQL Server Forums
 New to SQL Server Programming
 Convert Datetime into Varchar
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Johnph
Yak Posting Veteran

89 Posts

Posted - 05/06/2013 :  17:02:29  Show Profile  Reply with Quote
Convert(varchar(500),[DATE])FROM TABLE


My datetime fields looks like this:

2013-05-02 17:41:35.790

and I want the output as:

2013-05-02 17:41:35.790

but in varchar. My query is giving me May 5 2013.

Sorry if this has been asked before, but I could not find it.



Edited by - Johnph on 05/06/2013 17:03:40

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 05/06/2013 :  17:04:21  Show Profile  Reply with Quote
Use a specific style - for example
CONVERT(varchar(32),[DATE],120)
All the styles are listed here. http://msdn.microsoft.com/en-us/library/ms187928.aspx

Edited by - James K on 05/06/2013 17:04:36
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/07/2013 :  01:02:13  Show Profile  Reply with Quote
I dont understand why you want to change datatype if the value is already in the format you desire. In any case formatting should be done at front end wherever possible. Doing this at sql requires unnecessary convert/cast operations and would evenmake any further manipulations using these values difficult.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Johnph
Yak Posting Veteran

89 Posts

Posted - 05/07/2013 :  10:32:25  Show Profile  Reply with Quote
Thanks to both.
Visakh - I want to convert everything into a varchar so that I can concatenate a large string with multiple columns.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/08/2013 :  00:19:02  Show Profile  Reply with Quote
quote:
Originally posted by Johnph

Thanks to both.
Visakh - I want to convert everything into a varchar so that I can concatenate a large string with multiple columns.


Whats the purpose of doing this?
Is it for display or is it for sending them as delimited list to some other function? If latter, there are much better approaches like XML

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Johnph
Yak Posting Veteran

89 Posts

Posted - 05/08/2013 :  16:09:04  Show Profile  Reply with Quote
Yup, a delimited text file which I can use later for a bulk insert. Visakh, can you send me maybe a beginner's guide to XML with SQL server?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 05/08/2013 :  16:35:45  Show Profile  Reply with Quote
quote:
Originally posted by Johnph

Yup, a delimited text file which I can use later for a bulk insert. Visakh, can you send me maybe a beginner's guide to XML with SQL server?



Here are few useful links re: SQL XML


Stairway to XML at SSC: http://www.sqlservercentral.com/stairway/92778/

Jacob Sebastian's XQuery labs at BeyondRelational. I think he also has tutorials and other stuff: http://beyondrelational.com/modules/2/blogs/28/posts/10279/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

Michael Cole's book: http://www.amazon.com/Pro-Server-2008-Experts-Voice/dp/1590599837
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.07 seconds. Powered By: Snitz Forums 2000