Question 103502 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Convert to required date format with date datatype

I have input col as 12/03/08 viz dd/MM/yy . So I beed it in the format of dd/MM/yyyy . SO I have use the below transformation as :
toString(toDate(col,’dd/MM/yy’),’dd/MM/yyyy’)

This works fine.

But at the end i need to conver this col to date datatype with the same format as dd/MM/yyyy . When i do the CAST transformation like

In cast , i have converted it to date and given the format as dd/MM/yyyy .Its giving the date but in default format like dd-MM-yyyy . I need slashes instead of dashes.

I tried to do
toDate( col, ‘dd/MM/yyyy’) ,Still i get the default format dd-MM-yyyy.

How to fix this.

Tags:

2

Answers


  1. 0

    When you CAST in SQL for conversion, the resulted format is determined by the default format of your database. If you want to ensure that the date is formatted in a specific way, you can use the DATE_FORMAT function in combination with the toDate function.

    If you want to get the wanted dd/MM/yy to the format dd/MM/yyyy, you can use the following transformation:

    toString(toDate(col, ‘dd/MM/yy’), ‘dd/MM/yyyy’)

    This first converts the string to a type using toDate and then formats itt as a string with the wanted format using the toString function.

    However, if you then want to convert the string column back to a date datatype with the format dd/MM/yyyy, you cant simply use the CAST function with the desired format, as the resulting format will still be determined by the default format of your database. Instead, you can use the DATE_FORMAT function to format the date as a string with the desired format, and then convert it back to a date datatype using the toDate function.

    To convert the string column col back to a date datatype with the format dd/MM/yyyy, you can use the following transformation:

    toDate(DATE_FORMAT(col, ‘dd/MM/yyyy’), ‘dd/MM/yyyy’)

    This first formats the date in your string column as a string with the desired format using the DATE_FORMAT function, and then converts it back to a date datatype with the same format using the toDate function.

    Login or Signup to reply.
  2. 0

    This should help .

    CREATE TABLE T11 
(
T1 DATETIME 
)
INSERT INTO T11 VALUES ('12/03/08')

SELECT CONVERT(VARCHAR,T1,101) AS A FROM T11

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search