CODEWITHSUNDEEP
sqldate
Bart
Bart

Reputation: 11

Date conversion in SQL

I have a varchar column which holds date and time information in the below format:

20140813-14:01:05

This is in GMT. How do I change it to EST by subtracting 4? I used convert function and dateadd, but not getting the result I want.

Could someone please help?

thanks.

Upvotes: 0

Views: 181

Answers (4)

user13180603
user13180603

Reputation: 11

Date Conversion in SQL SQL Functions Like as any other programming language, SQL also supports a rich set of in-build functions. These functions greatly increase the ability to manipulate the information stored in a database. These functions are also referred as Oracle Functions. SQL functions accept arguments as an input and returns result as an output Arguments can be some constant values, or expressions, or column names related to tables. The general format of the SQL function is as given below: Function_name(argument1, argument2,…,argument) SQL functions can be categorized into two parts, based on whether they operate on a single row or a set or rows. more.

site: https://www.worldteachesus.com/

Upvotes: 1

Laxmi
Laxmi

Reputation: 3800

Consider this example and try it:

GMT :

SELECT GETDATE()

image1

EST :

SELECT DATEADD(HOUR, -4, CONVERT(varchar(20),GETDATE(),120))

image2

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35583

seems i'm too late, but a TSQL alternative:

DECLARE @String VARCHAR(50) = '20140813-14:01:05'

select convert(datetime,replace(@string,'-',' '))
;

Oracle 

with data as (
select '20140813-14:01:05' as string from dual
   )
select to_date(string,'YYYYMMDD-HH24:MI:SS') from data

edit: but in truth dates stored as strings is just plain bad.

Please identify dbms type in questions (esp. for date handling)

Upvotes: 0

AHiggins
AHiggins

Reputation: 7219

Assuming SQL Server and a completely consistent format, you could use:

DECLARE @String VARCHAR(50) = '20140813-14:01:05'

SELECT DATEADD(HOUR, -4, CAST(LEFT(@String, 8) AS DATETIME) + CAST(RIGHT(@String, 8) AS DATETIME)) AS ESTDate

Upvotes: 1

Related Questions