[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1412
  • Last Modified:

Date conversion from mm/dd/yyyy to yyyymmdd in sqlserver 2005

Hi!
I need convert date in the format mm/dd/yyyy to yyyymmdd
and also mm/dd/yy  to yyyymmdd format.I'm not able to find an appropriate solution for that.
Also how do I convert time in the format hh:mm:ss.xxx(xxx represent milli seconds)
to hh:mm:ss:xxxx .
How do I convert time in the format hhmmssx to hh:mm:ss:xxx?
How do I convert time in the format hh:mm:ss.xxx to hh:mm:ss?
How do I convert time in the format hh:mm:ss to hhmmss?
All of these in sql server 2005.
can I get the code if possible?
Please let me know ASAP.

Thanks in advance


0
amukta
Asked:
amukta
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Doc_McAlisterCommented:
You actually want to change what they are **stored as** or how they are formatted when you select them?
0
 
Anthony PerkinsCommented:
Datetime and smalldatetime are not stored in any regional format.  In order to convert for display purposes to some typical formats, take a look at the CONVERT() function in BOL
0
 
ursangelCommented:
Use the Convert function in SQL to convert the date format according to your choice.
You can specify the <Style> tag according to your choice.
For eg:- convert (datetime, <Column Name>, 112 ) will give you the format yyyymmdd
Like wise you can get different formats.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
amuktaAuthor Commented:
Hi! Doc McAlister,

I want to change the formatwhile selecting them.
Thanks,
Amukta
0
 
amuktaAuthor Commented:
Hi! ursangel,

Unable to do it with the solutio u've sent me, for soem reason.
Is there any other alternative.

Thanks,
0
 
Anthony PerkinsCommented:
>>Unable to do it with the solutio u've sent me, for soem reason.<<
Try posting your query and we can be more specific.

>>Is there any other alternative.<<
There is no other alternative with T-SQL.
0
 
Doc_McAlisterCommented:
More information is here:

http://technet.microsoft.com/en-us/library/aa226054(SQL.80).aspx

Select convert(datetime, columnName, 112) From tableName
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now