Convert Varchar data type to Datetime or Timestamp

Question

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 7 (of 7 total)

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

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

SELECT CAST(Col AS DATETIME)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101087 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: 16153 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: 101087 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: 16153 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?