• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

Convert Datatime in MSSQL

How do I convert a datetime field in MSSQL to MMDDYY format?

I have a select statement that I need tihe date, not the time portion that is stored in teh database.

The SQL that I have is :

SELECT     TXT_Name, CONVERT(DATETIME, DA_ProfileCreationDate, 'mmddyy') AS Expr1
FROM         TBL_Main

The DA_ProfileCreationDate has stored a date in the format of 2011-12-08 10:35:13.063 and I need the format 120811.

Any Ideas?
0
us1975mc
Asked:
us1975mc
  • 4
  • 2
  • 2
  • +2
1 Solution
 
TonyRebaCommented:
Trey:

CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
0
 
TempDBACommented:
you need it as mm-dd-yy , you can get as
select convert(datetime,DA_ProfileCreationDate,110)

if mm/dd/yy then
select convert(datetime,DA_ProfileCreationDate,101)
0
 
TonyRebaCommented:
CONVERT(VARCHAR(8), DA_ProfileCreationDate,1) AS [MM/DD/YY]
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
TonyRebaCommented:
101 will give you - mm/dd/yyyy   not YY
0
 
us1975mcAuthor Commented:
What I am needing is just mmddyy without the '-' or '/'
0
 
Scott PletcherSenior DBACommented:
As requested, in MMDDYY:

REPLACE(CONVERT(varchar(8), DA_ProfileCreationDate, 1), '/', '') AS [MMDDYY]
0
 
TonyRebaCommented:
Did you try :

CONVERT(VARCHAR(8), DA_ProfileCreationDate,1) AS [MM/DD/YY]

If you need no  '-' or '/'  then do as Scott suggests
0
 
TempDBACommented:
thanks tonyReba for correcting me with century part.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
date/time article I wrote:
http://www.experts-exchange.com/A_1499.html

will help you to understand ...
0
 
us1975mcAuthor Commented:
Thanks to everyone for the inputs.  I am learning a lot about datetime in SQL.  angelIII your article is WELL written and a great source of information.  ScottPletcher, you hit the hammer right on the nail.  Workd great!

Thanks to all.

just an old marine
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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