asked on

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?

TonyReba

Trey:

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

TempDBA

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)

TonyReba

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

TonyReba

101 will give you - mm/dd/yyyy not YY

us1975mc

ASKER

What I am needing is just mmddyy without the '-' or '/'

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TonyReba

Did you try :

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

If you need no '-' or '/' then do as Scott suggests

TempDBA

thanks tonyReba for correcting me with century part.

Guy Hengel [angelIII / a3]

date/time article I wrote:
https://www.experts-exchange.com/A_1499.html

will help you to understand ...

us1975mc

ASKER

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