Convert Varchar data type to Datetime or Timestamp

Question

Post reply

Convert Varchar data type to Datetime or Timestamp

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
June 8, 2022 at 3:59 pm

#4046314

select Convert(Varchar(100),yourcolumn,103) as Converted_Date from yourtbl
How can I get the end result as a Datetime or timestamp.
Regards,
SQLisAwe5oMe.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247172 More actions · Answer 1

Phil Parkin

SSC Guru

Points: 247172

June 8, 2022 at 4:12 pm

#4046323

SELECT CAST(Col AS DATETIME)

ScottPletcher SSC Guru Points: 101192 More actions · Answer 2

What is the data of "yourcolumn" (the original column)?

If it's already datetime, and you just want to strip the time off, then do this instead:

DATEADD(DAY, DATEDIFF(DAY, 0, yourcolumn), 0)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 3

I was trying something like this below..

select Convert(Varchar(100),dr_value,103) as Converted_Date from dr_tracking ;

What is the data of "yourcolumn" (the original column)?
- dr_value varchar(100)
  - dr_value - below is the value for that column
    04/26/2022 19:24:19

Regards,
SQLisAwe5oMe.

frederico_fonseca SSCoach Points: 16229 More actions · Answer 4

103 (dd/mm/yyyy) is the wrong format for that input

101 (mm/dd/yyyy) would be the right one

and from your last post you are trying to convert from a varchar to a datetime - so your convert would be

select Convert(datetime,dr_value,101) as Converted_Date from dr_tracking ;

sample example for you to check the differences in format - the first convert (103) fails, while the second works as expected

declare @Datex varchar(100) = '04/26/2022 19:24:19'
select Convert(datetime,@Datex,103) as date_103
select Convert(datetime,@Datex,101) as date_101

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 5

Thanks Frederico - I tried the below and getting the error below.

select Convert(datetime,dr_value,101) as Converted_Date from "users"."dr_tracking";

There was an error reading the results of the SQL statement.

The displayed results may be incorrect or incomplete.

Cannot convert '04/26/2022 19:24:19' to timestamp

SQLCODE=-157, ODBC 3 State="07006"

Line 1, column 1

select Convert(datetime,dr_value,101) as Converted_Date from "users"."dr_tracking"

Regards,
SQLisAwe5oMe.

ScottPletcher SSC Guru Points: 101192 More actions · Answer 6

SELECT Convert(datetime, Left(dr_value, 10), 101) as Converted_Date from dr_tracking ;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

frederico_fonseca SSCoach Points: 16229 More actions · Answer 7

that error looks like you are querying a Sybase database. is that correct?

and what driver are you using? vendor and version.

and... what are you doing with the returned value?

Convert Varchar data type to Datetime or Timestamp

Cookies on SQLServerCentral