Date and Time Format Conversion in SQL Server 2012

Question

I am using SQL Server 2012 sp1 .I have a table column with the following Date Time values.

BLDCHKDT
-----------------------
2013-06-19 00:00:00.000
2013-07-22 00:00:00.000
2013-08-21 00:00:00.000
2013-09-20 00:00:00.000
2013-11-18 00:00:00.000

I would like to retrieve the date and Time in the following formats:

Date: 19062013
Time: 00000000

Is it possible? I have reviewed the SQL Server help documentation for the FORMAT, CAST and CONVERT functions and I can’t seem to get any headway. So far I have attempted the following conversions:

N.B Please note that I am converting to Date time to string to facilitate a flat file export.

--Retrieving Date

SELECT [DATE-BLDCHKD] = CONVERT (VARCHAR (20), BLDCHKDT, 112)
FROM TABLEA

DATE-BLDCHKD
--------------------
20130619
20130722
20130821
20130920
20131118

--Retrieving Time
SELECT [TIME-BLDCHKD] = CONVERT (VARCHAR (20), BLDCHKDT, 24)
FROM TABLEA

TIME-BLDCHKD
--------------------
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00

I appreciate your insights. Thank you in advance.

Solution 1

You can do it this way:

declare @value datetime = getdate()

select 
    replace(convert(varchar(20), @value, 104), '.', '') date_part,
    left(replace(convert(varchar(20), @value, 114), ':', ''), 8) time_part

returns 23032014, 17174466 for 2014-03-23 17:17:44.660

Solution 2

Since you're using SQL Server 2012 you can use the FORMAT() function:

SELECT FORMAT(BLDCHKDT,'ddMMyyyy')
     , FORMAT(BLDCHKDT,'hhmmssfff')

Solution 3

Use 104th format for date part:

select replace (convert (varchar(10), @value, 104), '.', '') as date_part