datetime field in SQL 2008

Declare @dt datetime ='2015-05-01 05:00:00.000';

Select cast(@dt as date), cast(@dt As time)

Select allOpen in new window

This produces 2015-05-01      05:00:00.0000000

The displays need to be modified, brb

You can use the CONVERT function to format the date and time as strings, but note that in order to do date calculations you'll need to re-convert them into datetimes:

SELECT REPLACE(CONVERT(NVARCHAR, CAST('2015-05-01 05:00:00.000' AS DATE), 6), ' ', '-') AS StringDate,
    RIGHT(CONVERT(NVARCHAR, CAST('2015-05-01 05:00:00.000' AS DATETIME), 100), 7) AS StringTime

Select allOpen in new window

This site has a list of the different format options - I keep a printout on my wall for easy reference: http://www.sqlusa.com/bestpractices/datetimeconversion/

grendel777,

Tried your suggestion but got the following error message.
Type DATE is not a defined system type

Declare @dt datetime ='2015-05-01 05:00:00.000';
Select format(@dt,'dd-MMM-yy'), format(@dt,'hh:mm')

Select allOpen in new window

gives 01-May-15      05:00

grendel777,

I changed DATE to DATETIME and it gives what I need.
The time out put like "05:00PM". Is there anyway to make it "05:00 PM" with an extra space?
Thanks.

Declare @dt datetime ='2015-05-01 05:00:00.000';
Select format(@dt,'dd-MMM-yy'), format(@dt,'hh:mm') + ' ' + RIGHT(CONVERT(VARCHAR(30), @dt, 9), 2) 

Select allOpen in new window

returns
01-May-15      05:00 AM

>Type DATE is not a defined system type
Any chance this database is really 2005 or earlier?  The date data type was introduced in 2008.

Also, SQL expert Paul Maxwell has an article SQL Server Date Styles (formats) using CONVERT() that is an excellent date format reference.

< very wild guess that's out of my skillsets >  What is this databases' compatibility level set to?  If it's 2005, that may prevent code execution that include data types introduced after 2005.

Source / inspiration for asking...

mrong, do you still need help with this question?