CODEWITHSUNDEEP
sql-serversql-server-2008
Jiju John
Jiju John

Reputation: 821

datetime format in sql server 2008

I have used below code to convert a datetime to string, 

DECLARE @StartDate datetime = '08/07/2015 12:10 AM'
set @StartDate = dateadd(hour,12, @StartDate);

select CONVERT(VARCHAR(10),@StartDate, 101) + RIGHT(STUFF(CONVERT(VARCHAR(32),     @StartDate,100), 18, 0, ' '),8)

but I am getting output as "08/07/201512:10 PM" , there is no space between date and time, How can I correct this?

Upvotes: 0

Views: 213

Answers (3)

Gaurav Rajput
Gaurav Rajput

Reputation: 647

If i correctly understood your problem then there is small correction required in your code. I added +' '+ i.e. a blank space between your date convert and right stuff. Complete code is as given below.

DECLARE @StartDate DATETIME = '08/07/2015 12:10 AM'
SET @StartDate = DATEADD(HOUR,12, @StartDate);

SELECT CONVERT(VARCHAR(10),@StartDate, 101) +' '+
RIGHT(STUFF(CONVERT(VARCHAR(32),@StartDate,100), 18, 0, ' '),8)

Result

08/07/2015 12:10 PM

i.e. space between date and time also space between 12:10 and PM

To cover new case provided :

DECLARE @StartDate DATETIME = '08/07/2015 2:10 AM'
SET @StartDate = DATEADD(HOUR,12, @StartDate);

SELECT CONVERT(VARCHAR(10),@StartDate, 101) +' '+
LTRIM(RIGHT(STUFF(CONVERT(VARCHAR(32),@StartDate,100), 18, 0, ' '),8))

Result

08/07/2015 2:10 PM

i.e. no extra space when time is like 2:10 PM

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82524

Here is one way to do it:

DECLARE @StartDate datetime = '2015-08-07T00:10:00';

SET @StartDate = dateadd(hour,12, @StartDate);

SELECT  @StartDate As StartDate,
        CONVERT(CHAR(10), @StartDate, 101) + ' ' + --  DateString,
        SUBSTRING(CONVERT(CHAR(19), @StartDate, 100), 13, 5) + ' ' + -- TimeString
        RIGHT(CONVERT(CHAR(19), @StartDate, 100), 2) As DateString -- AM/PM

Result:

StartDate                   DateString
-----------------------     -------------------
2015-08-07 12:10:00.000     08/07/2015 12:10 PM

Upvotes: 1

Chris Pickford
Chris Pickford

Reputation: 9001

The following snippet will produce the output you've indicated in the question.

SET DATEFORMAT MDY;

DECLARE @StartDate DATETIME = '08-07-2015 12:10 AM';
SET @StartDate = DATEADD(HOUR, 12, @StartDate);

SELECT  CONVERT(VARCHAR, @StartDate, 103) + ' ' +
        CONVERT(VARCHAR, CAST(@StartDate AS TIME), 108) +
        CASE WHEN DATEPART(HOUR, @StartDate) < 12 THEN ' AM' ELSE ' PM' END;

N.B. As others have pointed out, you would be better off using ISO format for input dates.

Updated dateformat from DMY to MDY and explicitly adding AM/PM to the end.

Upvotes: 0

Related Questions