How to convert DateTime to VarChar

Question

I need to convert a value which is in a DateTime variable into a varchar variable formatted as yyyy-mm-dd format  without time part   How do I do that

User · Answer

select REPLACE CONVERT VARCHAR  FORMAT GETDATE    N dd MM yyyy hh mm ss tt                will give 05 05 2020 10 41 05 AM as a result

User · Answer

The shortest and the simplest way is    DECLARE  now AS DATETIME   GETDATE    SELECT CONVERT VARCHAR   now  23

User · Answer

The shortest and the simplest way is    DECLARE  now AS DATETIME   GETDATE    SELECT CONVERT VARCHAR   now  23

User · Answer

Try this SQL   select REPLACE CONVERT VARCHAR 24  GETDATE   103                          REPLACE CONVERT VARCHAR 24  GETDATE   114

User · Answer

declare  dt datetime  set  dt   getdate    select convert char 10   dt 120     I have fixed data length of char 10  as you want a specific string format

User · Answer

You can use DATEPART DATEPART  VARIABLE   For example   DECLARE  DAY INT  DECLARE  MONTH INT DECLARE  YEAR INT DECLARE  DATE DATETIME  DATE   GETDATE   SELECT  DAY   DATEPART DAY  DATE  SELECT  MONTH   DATEPART MONTH  DATE  SELECT  YEAR   DATEPART YEAR  DATE

User · Answer

This is how I do it  CONVERT NVARCHAR 10   DATE1  103

User · Answer

select REPLACE CONVERT VARCHAR  FORMAT GETDATE    N dd MM yyyy hh mm ss tt                will give 05 05 2020 10 41 05 AM as a result

User · Answer

Try the following   CONVERT VARCHAR 10  GetDate   102    Then you would need to replace the     with  -    Here is a site that helps http   www mssqltips com tip asp tip 1145

User · Answer

Try the following   CONVERT varchar 10    MyDateTimecolumn   20    For a full date time and not just date do      CONVERT varchar 23    MyDateTimecolumn   121    See this page for convert styles   http   msdn microsoft com en-us library ms187928 aspx OR SQL Server CONVERT   Function

User · Answer

Try the following   CONVERT VARCHAR 10  GetDate   102    Then you would need to replace the     with  -    Here is a site that helps http   www mssqltips com tip asp tip 1145

User · Answer

Try   select replace convert varchar  getdate    111       -      More on ms sql tips

User · Answer

You can convert your date in many formats  the syntaxe is simple to use    CONVERT  TheTypeYouWant    TheDateToConvert    TheCodeForFormating      CONVERT NVARCHAR 10   DATE OF DAY  103    gt  15 09 2016    The code is an integer  here 3 is the third formating without century  if you want the century just change the code to 103    In your case  i ve just converted and restrict size by nvarchar 10  like this    CONVERT NVARCHAR 10   MY DATE TIME  120    gt  2016-09-15   See more at   http   www w3schools com sql func convert asp  Another solution  if your date is a Datetime  is a simple CAST    CAST MY DATE TIME as DATE    gt  2016-09-15

User · Answer

With Microsoft SQL Server   Use Syntax for CONVERT   CONVERT   data type     length       expression     style       Example   SELECT CONVERT varchar d dateValue 1-9    For the style you can find more info here  MSDN - Cast and Convert  Transact-SQL

User · Answer

SQL Server 2012 has a new function   FORMAT   http   msdn microsoft com en-us library ee634924 aspx  and you can use custom date time format strings  http   msdn microsoft com en-us library ee634398 aspx  These pages imply it is also available on SQL2008R2  but I don t have one handy to test if that s the case   Example usage  Australian datetime     FORMAT VALUE  dd MM yyyy h mm ss tt

User · Answer

Try the following   CONVERT VARCHAR 10  GetDate   102    Then you would need to replace the     with  -    Here is a site that helps http   www mssqltips com tip asp tip 1145

User · Answer

-- This gives you the time as 0 in format  yyyy-mm-dd 00 00 00 000    SELECT CAST  CONVERT VARCHAR  GETDATE    101  AS DATETIME

User · Answer

With Microsoft SQL Server   Use Syntax for CONVERT   CONVERT   data type     length       expression     style       Example   SELECT CONVERT varchar d dateValue 1-9    For the style you can find more info here  MSDN - Cast and Convert  Transact-SQL

User · Answer

You don t say what language but I am assuming C   NET because it has a native DateTime data type  In that case just convert it using the ToString method and use a format specifier such as   DateTime d   DateTime Today  string result   d ToString  yyyy-MM-dd      However  I would caution against using this in a database query or concatenated into a SQL statement  Databases require a specific formatting string to be used  You are better off zeroing out the time part and using the DateTime as a SQL parameter if that is what you are trying to accomplish

User · Answer

CONVERT VARCHAR  GETDATE    23

User · Answer

With Microsoft Sql Server   -- -- Create test case -- DECLARE  myDateTime DATETIME SET  myDateTime    2008-05-03   -- -- Convert string -- SELECT LEFT CONVERT VARCHAR   myDateTime  120   10

User · Answer

-- This gives you the time as 0 in format  yyyy-mm-dd 00 00 00 000    SELECT CAST  CONVERT VARCHAR  GETDATE    101  AS DATETIME

User · Answer

The OP mentioned datetime format   For me  the time part gets in the way  I think it s a bit cleaner to remove the time portion  by casting datetime to date  before formatting   convert  varchar 10   convert  date   yourDate     111

User · Answer

For SQL Server 2008  You can use CONVERT and FORMAT together   For example  for European style  e g  Germany  timestamp   CONVERT VARCHAR  FORMAT GETDATE     dd MM yyyy HH mm ss    de-DE

User · Answer

You did not say which database  but with mysql here is an easy way to get a date from a timestamp  and the varchar type conversion should happen automatically    mysql gt  select date now      -------------    date now       -------------    2008-09-16      -------------  1 row in set  0 00 sec

User · Answer

This is how I do it  CONVERT NVARCHAR 10   DATE1  103

User · Answer

You can use DATEPART DATEPART  VARIABLE   For example   DECLARE  DAY INT  DECLARE  MONTH INT DECLARE  YEAR INT DECLARE  DATE DATETIME  DATE   GETDATE   SELECT  DAY   DATEPART DAY  DATE  SELECT  MONTH   DATEPART MONTH  DATE  SELECT  YEAR   DATEPART YEAR  DATE

User · Answer

With Microsoft Sql Server   -- -- Create test case -- DECLARE  myDateTime DATETIME SET  myDateTime    2008-05-03   -- -- Convert string -- SELECT LEFT CONVERT VARCHAR   myDateTime  120   10

User · Answer

You did not say which database  but with mysql here is an easy way to get a date from a timestamp  and the varchar type conversion should happen automatically    mysql gt  select date now      -------------    date now       -------------    2008-09-16      -------------  1 row in set  0 00 sec

User · Answer

Here s some test sql for all the styles   DECLARE  now datetime SET  now   GETDATE   select convert nvarchar MAX    now  0  as output  0 as style  union select convert nvarchar MAX    now  1   1 union select convert nvarchar MAX    now  2   2 union select convert nvarchar MAX    now  3   3 union select convert nvarchar MAX    now  4   4 union select convert nvarchar MAX    now  5   5 union select convert nvarchar MAX    now  6   6 union select convert nvarchar MAX    now  7   7 union select convert nvarchar MAX    now  8   8 union select convert nvarchar MAX    now  9   9 union select convert nvarchar MAX    now  10   10 union select convert nvarchar MAX    now  11   11 union select convert nvarchar MAX    now  12   12 union select convert nvarchar MAX    now  13   13 union select convert nvarchar MAX    now  14   14 --15 to 19 not valid union select convert nvarchar MAX    now  20   20 union select convert nvarchar MAX    now  21   21 union select convert nvarchar MAX    now  22   22 union select convert nvarchar MAX    now  23   23 union select convert nvarchar MAX    now  24   24 union select convert nvarchar MAX    now  25   25 --26 to 99 not valid union select convert nvarchar MAX    now  100   100 union select convert nvarchar MAX    now  101   101 union select convert nvarchar MAX    now  102   102 union select convert nvarchar MAX    now  103   103 union select convert nvarchar MAX    now  104   104 union select convert nvarchar MAX    now  105   105 union select convert nvarchar MAX    now  106   106 union select convert nvarchar MAX    now  107   107 union select convert nvarchar MAX    now  108   108 union select convert nvarchar MAX    now  109   109 union select convert nvarchar MAX    now  110   110 union select convert nvarchar MAX    now  111   111 union select convert nvarchar MAX    now  112   112 union select convert nvarchar MAX    now  113   113 union select convert nvarchar MAX    now  114   114 union select convert nvarchar MAX    now  120   120 union select convert nvarchar MAX    now  121   121 --122 to 125 not valid union select convert nvarchar MAX    now  126   126 union select convert nvarchar MAX    now  127   127 --128  129 not valid union select convert nvarchar MAX    now  130   130 union select convert nvarchar MAX    now  131   131 --132 not valid order BY style   Here s the result  output                   style Apr 28 2014  9 31AM          0 04 28 14                     1 14 04 28                     2 28 04 14                     3 28 04 14                     4 28-04-14                     5 28 Apr 14                    6 Apr 28  14                   7 09 31 28                     8 Apr 28 2014  9 31 28 580AM   9 04-28-14                     10 14 04 28                     11 140428                       12 28 Apr 2014 09 31 28 580     13 09 31 28 580                 14 2014-04-28 09 31 28          20 2014-04-28 09 31 28 580      21 04 28 14  9 31 28 AM         22 2014-04-28                   23 09 31 28                     24 2014-04-28 09 31 28 580      25 Apr 28 2014  9 31AM          100 04 28 2014                   101 2014 04 28                   102 28 04 2014                   103 28 04 2014                   104 28-04-2014                   105 28 Apr 2014                  106 Apr 28  2014                 107 09 31 28                     108 Apr 28 2014  9 31 28 580AM   109 04-28-2014                   110 2014 04 28                   111 20140428                     112 28 Apr 2014 09 31 28 580     113 09 31 28 580                 114 2014-04-28 09 31 28          120 2014-04-28 09 31 28 580      121 2014-04-28T09 31 28 580      126 2014-04-28T09 31 28 580      127 28               1435  9 31 28 580AM    130 28 06 1435  9 31 28 580AM    131   Make nvarchar max  shorter to trim the time  For example   select convert nvarchar 11   GETDATE    0  union select convert nvarchar max   GETDATE    0    outputs   May 18 2018 May 18 2018  9 57AM

User · Answer

Either Cast or Convert   Syntax for CAST   CAST   expression AS data type    length        Syntax for CONVERT   CONVERT   data type     length       expression     style       http   msdn microsoft com en-us library ms187928 aspx  Actually since you asked for a specific format   REPLACE CONVERT varchar 10   Date  102         -

User · Answer

Either Cast or Convert   Syntax for CAST   CAST   expression AS data type    length        Syntax for CONVERT   CONVERT   data type     length       expression     style       http   msdn microsoft com en-us library ms187928 aspx  Actually since you asked for a specific format   REPLACE CONVERT varchar 10   Date  102         -

User · Answer

Either Cast or Convert   Syntax for CAST   CAST   expression AS data type    length        Syntax for CONVERT   CONVERT   data type     length       expression     style       http   msdn microsoft com en-us library ms187928 aspx  Actually since you asked for a specific format   REPLACE CONVERT varchar 10   Date  102         -

User · Answer

Try the following   CONVERT varchar 10    MyDateTimecolumn   20    For a full date time and not just date do      CONVERT varchar 23    MyDateTimecolumn   121    See this page for convert styles   http   msdn microsoft com en-us library ms187928 aspx OR SQL Server CONVERT   Function

User · Answer

You don t say what language but I am assuming C   NET because it has a native DateTime data type  In that case just convert it using the ToString method and use a format specifier such as   DateTime d   DateTime Today  string result   d ToString  yyyy-MM-dd      However  I would caution against using this in a database query or concatenated into a SQL statement  Databases require a specific formatting string to be used  You are better off zeroing out the time part and using the DateTime as a SQL parameter if that is what you are trying to accomplish

User · Answer

You can convert your date in many formats  the syntaxe is simple to use    CONVERT  TheTypeYouWant    TheDateToConvert    TheCodeForFormating      CONVERT NVARCHAR 10   DATE OF DAY  103    gt  15 09 2016    The code is an integer  here 3 is the third formating without century  if you want the century just change the code to 103    In your case  i ve just converted and restrict size by nvarchar 10  like this    CONVERT NVARCHAR 10   MY DATE TIME  120    gt  2016-09-15   See more at   http   www w3schools com sql func convert asp  Another solution  if your date is a Datetime  is a simple CAST    CAST MY DATE TIME as DATE    gt  2016-09-15

User · Answer

Here s some test sql for all the styles   DECLARE  now datetime SET  now   GETDATE   select convert nvarchar MAX    now  0  as output  0 as style  union select convert nvarchar MAX    now  1   1 union select convert nvarchar MAX    now  2   2 union select convert nvarchar MAX    now  3   3 union select convert nvarchar MAX    now  4   4 union select convert nvarchar MAX    now  5   5 union select convert nvarchar MAX    now  6   6 union select convert nvarchar MAX    now  7   7 union select convert nvarchar MAX    now  8   8 union select convert nvarchar MAX    now  9   9 union select convert nvarchar MAX    now  10   10 union select convert nvarchar MAX    now  11   11 union select convert nvarchar MAX    now  12   12 union select convert nvarchar MAX    now  13   13 union select convert nvarchar MAX    now  14   14 --15 to 19 not valid union select convert nvarchar MAX    now  20   20 union select convert nvarchar MAX    now  21   21 union select convert nvarchar MAX    now  22   22 union select convert nvarchar MAX    now  23   23 union select convert nvarchar MAX    now  24   24 union select convert nvarchar MAX    now  25   25 --26 to 99 not valid union select convert nvarchar MAX    now  100   100 union select convert nvarchar MAX    now  101   101 union select convert nvarchar MAX    now  102   102 union select convert nvarchar MAX    now  103   103 union select convert nvarchar MAX    now  104   104 union select convert nvarchar MAX    now  105   105 union select convert nvarchar MAX    now  106   106 union select convert nvarchar MAX    now  107   107 union select convert nvarchar MAX    now  108   108 union select convert nvarchar MAX    now  109   109 union select convert nvarchar MAX    now  110   110 union select convert nvarchar MAX    now  111   111 union select convert nvarchar MAX    now  112   112 union select convert nvarchar MAX    now  113   113 union select convert nvarchar MAX    now  114   114 union select convert nvarchar MAX    now  120   120 union select convert nvarchar MAX    now  121   121 --122 to 125 not valid union select convert nvarchar MAX    now  126   126 union select convert nvarchar MAX    now  127   127 --128  129 not valid union select convert nvarchar MAX    now  130   130 union select convert nvarchar MAX    now  131   131 --132 not valid order BY style   Here s the result  output                   style Apr 28 2014  9 31AM          0 04 28 14                     1 14 04 28                     2 28 04 14                     3 28 04 14                     4 28-04-14                     5 28 Apr 14                    6 Apr 28  14                   7 09 31 28                     8 Apr 28 2014  9 31 28 580AM   9 04-28-14                     10 14 04 28                     11 140428                       12 28 Apr 2014 09 31 28 580     13 09 31 28 580                 14 2014-04-28 09 31 28          20 2014-04-28 09 31 28 580      21 04 28 14  9 31 28 AM         22 2014-04-28                   23 09 31 28                     24 2014-04-28 09 31 28 580      25 Apr 28 2014  9 31AM          100 04 28 2014                   101 2014 04 28                   102 28 04 2014                   103 28 04 2014                   104 28-04-2014                   105 28 Apr 2014                  106 Apr 28  2014                 107 09 31 28                     108 Apr 28 2014  9 31 28 580AM   109 04-28-2014                   110 2014 04 28                   111 20140428                     112 28 Apr 2014 09 31 28 580     113 09 31 28 580                 114 2014-04-28 09 31 28          120 2014-04-28 09 31 28 580      121 2014-04-28T09 31 28 580      126 2014-04-28T09 31 28 580      127 28               1435  9 31 28 580AM    130 28 06 1435  9 31 28 580AM    131   Make nvarchar max  shorter to trim the time  For example   select convert nvarchar 11   GETDATE    0  union select convert nvarchar max   GETDATE    0    outputs   May 18 2018 May 18 2018  9 57AM

User · Answer

The OP mentioned datetime format   For me  the time part gets in the way  I think it s a bit cleaner to remove the time portion  by casting datetime to date  before formatting   convert  varchar 10   convert  date   yourDate     111

User · Answer

DECLARE  DateTime DATETIME SET  DateTime    2018-11-23 10 03 23  SELECT CONVERT VARCHAR 100   DateTime 121

User · Answer

Try this SQL   select REPLACE CONVERT VARCHAR 24  GETDATE   103                          REPLACE CONVERT VARCHAR 24  GETDATE   114

User · Answer

With Microsoft Sql Server   -- -- Create test case -- DECLARE  myDateTime DATETIME SET  myDateTime    2008-05-03   -- -- Convert string -- SELECT LEFT CONVERT VARCHAR   myDateTime  120   10

User · Answer

Try   select replace convert varchar  getdate    111       -      More on ms sql tips

User · Answer

Write a function  CREATE FUNCTION dbo TO SAP DATETIME  input datetime  RETURNS VARCHAR 14  AS BEGIN     DECLARE  ret VARCHAR 14      SET  ret   COALESCE SUBSTRING REPLACE REPLACE REPLACE CONVERT VARCHAR 26    input  25   -                      1 14   00000000000000        RETURN  ret END

User · Answer

You did not say which database  but with mysql here is an easy way to get a date from a timestamp  and the varchar type conversion should happen automatically    mysql gt  select date now      -------------    date now       -------------    2008-09-16      -------------  1 row in set  0 00 sec

User · Answer

You don t say what language but I am assuming C   NET because it has a native DateTime data type  In that case just convert it using the ToString method and use a format specifier such as   DateTime d   DateTime Today  string result   d ToString  yyyy-MM-dd      However  I would caution against using this in a database query or concatenated into a SQL statement  Databases require a specific formatting string to be used  You are better off zeroing out the time part and using the DateTime as a SQL parameter if that is what you are trying to accomplish

User · Answer

You don t say what language but I am assuming C   NET because it has a native DateTime data type  In that case just convert it using the ToString method and use a format specifier such as   DateTime d   DateTime Today  string result   d ToString  yyyy-MM-dd      However  I would caution against using this in a database query or concatenated into a SQL statement  Databases require a specific formatting string to be used  You are better off zeroing out the time part and using the DateTime as a SQL parameter if that is what you are trying to accomplish

User · Answer

Try the following   CONVERT varchar 10    MyDateTimecolumn   20    For a full date time and not just date do      CONVERT varchar 23    MyDateTimecolumn   121    See this page for convert styles   http   msdn microsoft com en-us library ms187928 aspx OR SQL Server CONVERT   Function

User · Answer

You did not say which database  but with mysql here is an easy way to get a date from a timestamp  and the varchar type conversion should happen automatically    mysql gt  select date now      -------------    date now       -------------    2008-09-16      -------------  1 row in set  0 00 sec

User · Answer

Try the following   CONVERT VARCHAR 10  GetDate   102    Then you would need to replace the     with  -    Here is a site that helps http   www mssqltips com tip asp tip 1145

User · Answer

DECLARE  DateTime DATETIME SET  DateTime    2018-11-23 10 03 23  SELECT CONVERT VARCHAR 100   DateTime 121

User · Answer

CONVERT VARCHAR  GETDATE    23

User · Answer

With Microsoft Sql Server   -- -- Create test case -- DECLARE  myDateTime DATETIME SET  myDateTime    2008-05-03   -- -- Convert string -- SELECT LEFT CONVERT VARCHAR   myDateTime  120   10

User · Answer

SQL Server 2012 has a new function   FORMAT   http   msdn microsoft com en-us library ee634924 aspx  and you can use custom date time format strings  http   msdn microsoft com en-us library ee634398 aspx  These pages imply it is also available on SQL2008R2  but I don t have one handy to test if that s the case   Example usage  Australian datetime     FORMAT VALUE  dd MM yyyy h mm ss tt

User · Answer

Write a function  CREATE FUNCTION dbo TO SAP DATETIME  input datetime  RETURNS VARCHAR 14  AS BEGIN     DECLARE  ret VARCHAR 14      SET  ret   COALESCE SUBSTRING REPLACE REPLACE REPLACE CONVERT VARCHAR 26    input  25   -                      1 14   00000000000000        RETURN  ret END

User · Answer

declare  dt datetime  set  dt   getdate    select convert char 10   dt 120     I have fixed data length of char 10  as you want a specific string format

User · Answer

Either Cast or Convert   Syntax for CAST   CAST   expression AS data type    length        Syntax for CONVERT   CONVERT   data type     length       expression     style       http   msdn microsoft com en-us library ms187928 aspx  Actually since you asked for a specific format   REPLACE CONVERT varchar 10   Date  102         -

User · Answer

Try the following   CONVERT varchar 10    MyDateTimecolumn   20    For a full date time and not just date do      CONVERT varchar 23    MyDateTimecolumn   121    See this page for convert styles   http   msdn microsoft com en-us library ms187928 aspx OR SQL Server CONVERT   Function

User · Answer

For SQL Server 2008  You can use CONVERT and FORMAT together   For example  for European style  e g  Germany  timestamp   CONVERT VARCHAR  FORMAT GETDATE     dd MM yyyy HH mm ss    de-DE

[sql] How to convert DateTime to VarChar

Examples related to sql

Examples related to sql-server

Examples related to datetime

Examples related to sql-convert