logo
Home JAVASCRIPTJAVACHASHPYTHONPHPCPPIOSSQLHTML
down
shadow

How to change datetime format of varchar datatype column


How to change datetime format of varchar datatype column

Content Index :

How to change datetime format of varchar datatype column
Tag : sql-server , By : RichAA
Date : November 29 2020, 01:01 AM

To fix the issue you can do You can use Try_Convert() in concert with IsNull()
To be clear. The result is still a string
Declare @YourTable table (Value varchar(50))
Insert Into @YourTable values
 ('268142')
,('Jan 1 1900 12:04PM')

Select *
     ,NewVal = IsNull(format(try_convert(datetime,Value),'yyyy-MM-dd HH:mm:ss'),Value)
 From  @YourTable

Value                NewVal
268142              268142
Jan 1 1900 12:04PM  1900-01-01 12:04:00

NewVal = IsNull(try_convert(varchar(50),try_convert(datetime,Value),120),Value)

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Tags :

How to change datetime format of varchar datatype column , I want to change datatype of a column [varchar] to [datetime] in sql without losing the data , how to change the datatype varchar(50) to datetime datatype in a table , How can I change the format of the dates produced when using ALTER COLUMN to co ,

How can I change the format of the dates produced when using ALTER COLUMN to convert a datetime column to varchar?

Tag : sql , By : Matthew Steed
Date : March 29 2020, 07:55 AM
I hope this helps . Bad, Bad idea...never ever store dates in varchar columns, now you will get garbage in there in different formats Also why varchar(16) when you want yyyyMMdd?
if you want the output to be in a different format do it in the presentation layer or use convert
SELECT CONVERT(CHAR(8),GETDATE(),112)

UPDATE Table 
SET LastUpdateDate  =  CONVERT(CHAR(8),(CONVERT(DATETIME,CONVERT(varchar,LastUpdateDate))),112)

conversion of a varchar datatype to a datetime datatype resulted in an out-of-range value

Tag : sql , By : Xander
Date : March 29 2020, 07:55 AM
Hope that helps Type 103 requires that you have datetimes with the European date/month order: 'dd/mm/yyyy'
If you store month first, this may result in this error (say for '01/13/2012')

What column size(varchar datatype) is needed to store an image in STRING format?

Tag : android , By : Allen
Date : March 29 2020, 07:55 AM
Any of those help AFAIK sqlite uses charset conversation for TEXT columns, consider using BLOB type to store image.

how to change the datatype varchar(50) to datetime datatype in a table

Tag : sql-server-2008 , By : haver
Date : March 29 2020, 07:55 AM
this will help First convert your string with appropriate style(http://www.sqlhub.com/2009/04/list-of-all-available-datetime-format.html). Then Alter your column.
UPDATE TableName SET TravelDate =  CONVERT(DATETIME, TravelDate, 103)

ALTER TABLE dbo.TableName ALTER COLUMN TravelDate DATETIME NULL[NOT NULL]
GO

I want to change datatype of a column [varchar] to [datetime] in sql without losing the data

Tag : sql-server , By : Munir
Date : March 29 2020, 07:55 AM
it helps some times Create TEMP table to copy values from your table, after It copy values from table to temp table. After It you can truncate your table or column with dates, after It you can change datatype of nvarchar column to datetime, after It copy values from temp table to normal table, You can do something like:
-- Creation of sample table
CREATE TABLE ChangeColType
(
    Data NVARCHAR(20)
)

-- Inserting sample data 
INSERT INTO ChangeColType VALUES ('10:00 AM')

-- Creatig temp table
CREATE TABLE #TempValues
(
    Data NVARCHAR(50)
)

-- Copying values from table to temp table
INSERT INTO #TempValues
SELECT Data 
FROM ChangeColType

-- Truncating your table
TRUNCATE TABLE ChangeColType

-- Changing datatype of your column
ALTER TABLE ChangeColType
ALTER COLUMN Data DATETIME

-- Copying back values from temp table to your table
INSERT INTO ChangeColType
SELECT Data 
FROM #TempValues

-- Selecting values from table after datatype changed
SELECT CONVERT(varchar(15),CAST(Data AS TIME),100)   
FROM ChangeColType

-- Dropping temp table
DROP TABLE #TempValues
Related Posts Related QUESTIONS :
  • Split BulkColumn into Rows
  • Naming a Column using Variable
  • T-SQL Slowly Changing Dimension - Multiple Updates, Single Action Output?
  • SQL Output as XML namespace
  • Using Wildcard For Range of Characters In T-SQL
  • Azure SQL Server Elastic query with ApplicationIntent ReadOnly
  • How to get date ranges according to date data inside database table
  • How to get those records that contains every items mentioned in where clause
  • SQL Trigger on insert, only for new rows
  • Recursively replace string using a CTE
  • How to merge multiple column into one column with same table in SQL Server?
  • Designing stored procedure to work with Multiple Tables which have different formats and logic
  • How to select SQL Server table as tab delimited string
  • How to delete specific range of records from a GROUP BY query?
  • SAS dsnless connection to SQL server not working even if OLEDB is installed
  • How to correct an error in SELECT statement
  • All cases when check constraints are executed for each row
  • Exporting database data to csv in classic asp
  • Why does this char value not convert to datetime?
  • How to get file type from binary data in SQL Server File table?
  • Reading derived column expression
  • How do I persist sales price in an orders details table?
  • How to extract data from xml nodes as table from column?
  • Removing Leading and Trailing Zeros along with a Case
  • How to insert new line Char(13) in T-SQL
  • Get first date record from multiple records
  • SQL Server Procedure Returning -1
  • Underscore in Where clause yields unexpected result, why?
  • How to calculate the SID for new logins?
  • Purpose of defining parameters in Access query
  • Windows API call "RegGetValueW" returned error code: 0
  • How to convert CURSOR based query in to SET based
  • Why my main query is running successfully at the same time as subquery returns an error?
  • Rounding up starting 6
  • How to use INSERT INTO OPENROWSET to export to Excel without being an admin on the target server
  • SQL SERVER job is not executing SSIS
  • How to convert Row by row execution in to SET based approach in SQL
  • How to copy databases automatically from one server to another in efficient way (not backup and restore)
  • How to combine rows of similar results into one?
  • Convert HH:MM TT to Time and append onto date
  • Dynamic stored procedure to update a table
  • How do you debug or step through the code in SQL Server Management Studio 18?
  • How to import .bak file into SQL Server?
  • SQL Server Wildcard Length
  • SQL Server : column data extraction
  • The nullability property of arithmetic operators over floating point data type in SQL Server
  • MD5 Hashing in SQL Server
  • How to extract every 7 characters of an nvarchar into another table?
  • ARX Anonymization Tool - supported databases
  • What are good practices for granting database permissions to a web service connection?
  • Go MSSQL Connection
  • SQL Server select query very slow
  • How to delete from multiple tables in one DELETE statement in a .cfc file?
  • Is it possible to get "NT AUTHORITY\NETWORK SERVICE" user independent of language?
  • How to select date and previous date
  • Cleaning dupes and keeping max data
  • Why would adding a simple field to select change the amount of returned rows SQL
  • Read file in SSIS Project into a variable
  • A simple pivot but can't get my head around it - please assist..:-)
  • SQL Server INSERT INTO using transaction
    		•
    shadow
    Privacy Policy - Terms - Contact Us © scrbit.com