CODEWITHSUNDEEP
sql
Slim
Slim

Reputation: 1744

ms sql convert string to date changing the order from yyyy-mm-dd to dd.mm.yyyy

I have a field named VALUE of VARCHAR2 type

The string is in this format: yyyy-mm-dd Is it possible to change it to: dd-mm-yyyy

An then (again if possible) to convert it to date in this format?

What I have tried is:

SELECT convert(varchar, getdate(), 110)

but it's only for the current time. Is there a way to do this?

Edit: Here my code is:

select
p1.VALUE as Sdate
from Process p 
LEFT JOIN PARAMETER p1 on p1.WP_ID=p.ID AND p1.NAME = 'Sdate'
WHERE p.TYPE = 'Marketing' and convert(varchar, convert(date,p1.value), 103) ='22.08.2012'

Upvotes: 1

Views: 5804

Answers (2)

Himanshu
Himanshu

Reputation: 32602

104 will convert in dd.mm.yyyy format

SELECT CONVERT(VARCHAR, GETDATE(), 104)

Your query should be: 

SELECT p1.VALUE AS Sdate
FROM Process p 
LEFT JOIN PARAMETER p1 
       ON p1.WP_ID = p.ID 
      AND p1.NAME = 'Sdate'
WHERE p.TYPE = 'Marketing' 
  AND CONVERT(VARCHAR, CONVERT(DATE, p1.value), 104) = '22.08.2012'

See an example in this SQLFiddle.

Note that you should keep the column type as DATE where you want to store dates. Also, always prefer yyyyMMdd format.

Have a look at CAST and CONVERT (Transact-SQL)

Upvotes: 3

Vikram Jain
Vikram Jain

Reputation: 5588

        SELECT convert(varchar, columnname, 103) from tablename
        or
        SELECT convert(varchar, convert(date,'2012/11/01'), 103)
        or
       SELECT convert(varchar, convert(date,columnname), 103) from tablename
=================================
select
p1.VALUE as Sdate
from Process p 
LEFT JOIN PARAMETER p1 on p1.WP_ID=p.ID AND p1.NAME = 'Sdate'
WHERE p.TYPE = 'Marketing' and convert(varchar, convert(date,p1.value), 103) ='22/08/2012'

Upvotes: 2

Related Questions