CODEWITHSUNDEEP
sqlsql-serverdatedatetime
DonOfDen
DonOfDen

Reputation: 4098

SQL Select Specific Date format dd/mm/YYYY hh:mm:ss

I want to select a record from a table where the date format stored as 'Jan 27 2015 12:00AM' When I select I should covert the date to the following format '27/01/2015 00:00:00'.

I tried the following it works for Date but not for TIME. So I used get date. 

select  CONVERT(VARCHAR(10), CONVERT(date,StartDate,106), 103) + ' ' 
+ convert(VARCHAR(8), GETDATE(), 14) as StartDate from Logistic

Can some one help me to convert the date format correctly.

I tried the Answer:

CONVERT(VARCHAR(10), StartDate, 103) 
                        + ' ' + CONVERT(VARCHAR(8), StartDate, 108)

Result: Aug 25 201 Aug 25 2

Upvotes: 3

Views: 4204

Answers (6)

A_Sk
A_Sk

Reputation: 4630

Try:

declare @strDate varchar(max)='Jan 27 2015 12:00AM'


select Convert(varchar(50),CONVERT(datetime, @strDate, 100),103)+' '+
cast(convert(time,@strDate,108) as varchar(8))

Upvotes: 1

ughai
ughai

Reputation: 9890

You can convert it to date using style 100 and convert back to VARCHAR using style 120.

DECLARE @yourdate VARCHAR(100)='Jan 27 2015 12:00AM';

SELECT REPLACE(CONVERT(VARCHAR(50),CONVERT(DATETIME,@yourdate,100),120),'-','/');

Output

2015/01/27 00:00:00

Upvotes: 1

M.Ali
M.Ali

Reputation: 69554

Convert MMM dd yyyy HH:MM[AM|PM] to dd/MM/yyyy hh:mm:ss

Declare @Date VARCHAR(20) = 'Jan 27 2015 12:05AM'

SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, @Date),103)
          + ' ' + CONVERT(VARCHAR(8), CONVERT(DATETIME, @Date),108)


    Result:    27/01/2015 00:05:00

Upvotes: 3

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

You can do It in following, first convert to DATE and TIME, after It convert to NVARCHAR datatype:

DECLARE @dt VARCHAR(200)='Jan 27 2015 10:00AM';

SELECT CONVERT(NVARCHAR(20),CONVERT(DATE,@dt,103),103) + ' ' + 
       CAST(CAST(@dt AS TIME(0)) AS NVARCHAR(20));

OUTPUT:

27/01/2015 10:00:00

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Check this: Hope this answers your question...

EDIT: Hope this is the format you need:L

declare @d VARCHAR(100) ='Jan 27 2015 11:59AM';

SELECT CONVERT(VARCHAR(10),CAST(@d AS DATE),103) + ' ' + CAST(CAST(@d AS TIME) AS VARCHAR(8))

I'm not used to the AM/PM format and I had assumed, that 12:00AM is midday and 12:00PM is midnight, but it is exactly the other way round... I'm a little puzzled at the moment... ;-)

declare @d VARCHAR(100) ='Jan 27 2015 11:59AM';
SELECT CONVERT(DATETIME,@d,100);

SET @d ='Jan 27 2015 12:00AM';
SELECT CONVERT(DATETIME,@d,100);

SET @d ='Jan 27 2015 12:01AM';
SELECT CONVERT(DATETIME,@d,100);

--some more, just to check...
SET @d ='Jan 27 2015 10:00AM';
SELECT CONVERT(DATETIME,@d,100);

SET @d ='Jan 27 2015 10:00PM';
SELECT CONVERT(DATETIME,@d,100);

SET @d ='Jan 27 2015 00:00AM';
SELECT CONVERT(DATETIME,@d,100);

Upvotes: 1

jumxozizi
jumxozizi

Reputation: 649

https://msdn.microsoft.com/en-us/library/ms187928.aspx

Convert your varchar (from format 100) to datetime. Then convert the datetime you get, into formats 103 (for date) and 108 (for time). Finally concatenate the format 103 date and format 108 time.

--  'Jan 27 2015 12:00AM' --> '27/01/2015 00:00:00'
SELECT  CONVERT(VARCHAR(25),CONVERT(DATETIME,'Jan 27 2015 12:00AM',100),103)
  +' '+ CONVERT(VARCHAR(25),CONVERT(DATETIME,'Jan 27 2015 12:00AM',100),108)

Upvotes: 1

Related Questions