0

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.

Robby Cornelissen
  • 91,784
  • 22
  • 134
  • 156
Bart
  • 11
  • 6
    Which DBMS are you using? Postgres? Oracle? –  Aug 15 '14 at 13:30
  • 3
    Also, please share what you tried, and explain why the result is wrong. – Mureinik Aug 15 '14 at 13:33
  • 1
    May be this would help assuming its SQL Server. http://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server – Punter015 Aug 15 '14 at 13:44
  • date handling is the very least standard area amongst dbms implementations - the request to identify your dbms isn't just because we are curious - we actually need to know so you get a relevant answer. always identify the dbms anyway. include version too if you can (it also matters with dates) – Paul Maxwell Aug 15 '14 at 13:59

4 Answers4

1

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
AHiggins
  • 7,029
  • 6
  • 36
  • 54
  • I was able to use substring to separate out the time part and then use convert and dateadd functions. A little round about way to achieve the result, but it worked. Thanks everyone for the help. – Bart Aug 15 '14 at 14:19
1

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/

user13180603
  • 11
  • 1
0

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)

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
0

Consider this example and try it:

GMT :

SELECT GETDATE()

image1

EST :

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

image2

Laxmi
  • 3,830
  • 26
  • 30