CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
frans
frans

Reputation: 181

Convert Datetime format to varchar format style workaround

I have a datetime column that I need to display in the following format:

YYYY/MM/DD 00:00:00:000

With CONVERT, I can't find any suitable styles.

The / in the format is really important as it needs to be compared with a VARCHAR column that has text YYYY/MM/DD 00:00 as part of the description like below:

If I can find a way/style then I can use a SUBSTRING function to compare it to the value below:

Diary item added for : 2013/08/20 14:12

I have had a look at:

http://databases.aspfaq.com/database/what-are-the-valid-styles-for-converting-datetime-to-string.html

But, I can't find any sufficient styles that has / 's. Only -'s.

Upvotes: 3

Views: 11869

Answers (3)

Albert Laure
Albert Laure

Reputation: 1722

Well you can convert both Dates in any format before you compare the two just use any of these: run this and you can select from them, make sure to convert both dates before Comparing

Select    
    convert(varchar, GetDate(), 100) as '100 Conversion',    
    convert(varchar, GetDate(), 101) as '101 Conversion',    
    convert(varchar, GetDate(), 102) as '102 Conversion',    
    convert(varchar, GetDate(), 103) as '103 Conversion',    
    convert(varchar, GetDate(), 104) as '104 Conversion',    
    convert(varchar, GetDate(), 105) as '105 Conversion',    
    convert(varchar, GetDate(), 106) as '106 Conversion',    
    convert(varchar, GetDate(), 107) as '107 Conversion',    
    convert(varchar, GetDate(), 108) as '108 Conversion',    
    convert(varchar, GetDate(), 109) as '109 Conversion',    
    convert(varchar, GetDate(), 110) as '110 Conversion',    
    convert(varchar, GetDate(), 111) as '111 Conversion',    
    convert(varchar, GetDate(), 112) as '112 Conversion',    
    convert(varchar, GetDate(), 113) as '113 Conversion',    
    convert(varchar, GetDate(), 114) as '114 Conversion',    
    convert(varchar, GetDate(), 120) as '120 Conversion',    
    convert(varchar, GetDate(), 121) as '121 Conversion',    
    convert(varchar, GetDate(), 126) as '126 Conversion',    
    convert(varchar, GetDate(), 130) as '130 Conversion',    
    convert(varchar, GetDate(), 131) as '131 Conversion'

Or Use this 

select REPLACE(convert(varchar, GetDate(), 121),'-','/')

Upvotes: 3

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

You can use the style that most closely resembles what you want to compare on and use REPLACE to replace the - with \.

SELECT REPLACE(CONVERT(<yourstyle>),'-','/');

Edit Cudo's to @bluefeet

SELECT REPLACE(CONVERT(varchar(23), yourdate, 121),'-','/');

From Technet: Cast and Convert

21 or 121 (2) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)

Upvotes: 5

DB_learner
DB_learner

Reputation: 1026

Why cant you do the reverse. Convert the varchar column to date instead of converting date to varchar

Upvotes: 2

Related Questions