Questions tagged [date-format]

Used for handling non-default style character-strings representing the date and time. This is predicated on handling date and time styles from different nations and/or languages.

Filter by
Sorted by
Tagged with
0
votes
0answers
5 views

Redshift/Athena: Most efficient way to cast timestamps of varying precision to date in query?

I'm receiving a stream of log files to an S3 bucket and querying this data through Amazon Athena (Redshift). Contained in the log files are timestamps with the seconds field having decimals to the ...
0
votes
0answers
13 views

MySQL date_format German date value not change to date type

I have a column that is varchar and the value of the the data is a date (i.e. 31.05.2019) I'm trying to convert the varchar field to a date field using the following function: date_format(...
0
votes
3answers
41 views

How to update Different Date formats to one specific datetime formate

I need advise on conversion of date time, below you may see the example data with images for current and expected output Input Table Expected Output
-1
votes
1answer
21 views

how do I get date field in certain string format

my table has below columns A: date B: date C: and many more columns, I do not want use to_char(A, date_format), how I can do select * from mytable, and the returned A, B column are in certain ...
0
votes
1answer
32 views

Are iSeries AS/400 (DB2?), Lotus 1-2-3, and Excel date/time formats the same? [closed]

Are the formats for dates/times from AS/400, to Lotus 1-2-3 that carried over into excel...with the date being the number of days since 1900-1-1 when mistaking 1900 for a leap year as an integer, and ...
0
votes
0answers
39 views

SQL Server group by date, time between status

I have a table that looks like the following: id_usr dt_event Sequence 0000001 2015-08-01 14:57:21.737 1 0000001 2015-08-06 15:34:21.920 2 0000001 ...
-6
votes
1answer
38 views

SQL date question [closed]

I'm a novice and need help. Trying to pull data from a table that will return info (various investment trade info) from the previous business day. Is there a command or function for that? thx
1
vote
2answers
85 views

How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause

How would I cast a column value (a "non-MySQL" DATE string retained as a VARCHAR) to a "MySQL date" so it can be used for sorting? I have dates in the format of 06-02-2019 (6 feb) but MySQL itself ...
1
vote
1answer
39 views

Searching through dates query not working?

I'm trying to query the recent week rows by using dates. BUT the query is not returning any result. It takes forever to execute. I checked my DataBase where date is saved in 1446976737 format which ...
0
votes
2answers
194 views

How to format a date field into diferente languages in the same command? (multiple locales)

The following will return the day in Italian: SET lc_time_names = 'it_IT'; select date_format('2018/01/01','%W') as day_italian; However I need to convert or format the date into multiple languages,...
1
vote
0answers
45 views

better format using nanodbc R on Windows

Using R-3.5 and odbc (which uses nanodbc), I connect via ODBC and the ODBC Driver 13 for SQL Server ODBC driver on windows 10. DBI::dbGetQuery(odbccon, "select SYSDATETIMEOFFSET() as now") # ...
-1
votes
2answers
67 views

TSQL Query to Remove Dates in Multiple Formats from Text Values [closed]

I have a table with list of text values that contain dates in any format types. I need a TSQL query to remove the dates from each row in the table. The date samples are below. What is the best way ...
5
votes
1answer
388 views

Getting year DATEPART from Unix date

In my database, the dates are retained as days elapsed since/before 01/01/1970, which I understand to be a Unix date format, eg. today is 23rd May 2018 and the value is 17674. I'm trying to run a ...
1
vote
0answers
690 views

PostgreSQL datestyle / OS locale issue

The problem of this issue has been identified and can be treated as resolved. (see the comments bellow) I try to install a web-application on a server (debian) and on a local machine (ubuntu). When ...
1
vote
1answer
3k views

Incorrect value for str_to_date

I'm running a simple procedure that reads from a VARCHAR column and inserts data into a TIME column, using this format: STR_TO_DATE(vTestTime, '%H:%i'). I'm getting the error SQL Error (1411): ...
1
vote
0answers
1k views

MySQL 5.6 - ERROR 1292 (22007): Incorrect datetime value: '2015-03-29 01:01:12'

I am trying to copy logs from one of my MySQL 5.6 databases to another. Unfortunately, it keeps failing when it tries to insert one of the times with the following error message: ERROR 1292 (22007): ...
1
vote
1answer
447 views

Formatting Timestamp in Default Constraint

Can you format a TIMESTAMP WITH TIME ZONE column in a CREATE TABLE default constraint in Postgres v10.x? Example Code: create_date TIMESTAMP WITH TIME ZONE DEFAULT to_char(current_timestamp, 'DD-...
1
vote
1answer
2k views

Why does JSONB show a different date format than Javascript?

With Javascript's .toISOString(); and .toJSON() I get (new Date()).toISOString(); 2018-01-30T12:04:13.153Z Using PostgreSQL when I return a date, I get # SELECT to_jsonb('2018-01-30T12:04:13.153Z'::...
1
vote
1answer
716 views

MySQL : DATE_FORMAT() vs. SUBSTR()

I'm currently working on MYSQL DB optimizations. I'm looking around some slow queries and I try to optimize some of them. In my app, for statistics purposes, I'm querying and -very- big table (...
2
votes
1answer
1k views

Parsing COPY's binary format to access a tsrange

How is tsrange stored in binary? For example create table CREATE TABLE public.test (t tsrange); INSERT INTO test VALUES ('[2010-01-01 14:30, 2010-01-01 15:30)'); INSERT INTO test VALUES ('[2011-01-...
3
votes
3answers
2k views

Get all CONVERT(datetime) styles (formats)

I know this is kinda stupid to ask but is there a Transact-SQL function or anything that returns all CONVERT(datetime) styles (formats)? Something like fnDateFormats will return all date formats: ...
0
votes
0answers
88 views

SQL Server 2014 datetime conversion

I have a date formatted as follows: 00:50:34 Jun 25, 2017 PDT .. actually I have it in this format 00%3A50%3A34+Jun+25%2C+2017+PDT but can easily transform in the above. I was wondering if there'...
0
votes
1answer
1k views

Microsoft SQL Server 2008+ TIMESTAMP problem

I'm currently facing a Problem, where the SQL Server refuses my Timestamps. I am accessing the Server via EclipseLink (Java) but this shall not be the problem, as i can test the queries on the Server ...
2
votes
1answer
93 views

International Components for Unicode (ICU) Date Formatter for MySQL?

Regarding to ICU - International Components for Unicode definition: ICU is a mature, widely used set of C/C++ and Java libraries providing Unicode and Globalization support for software ...
1
vote
1answer
727 views

Best data upload formats

Most of our automatically generated files contain integers, decimals, date, time, and sometimes varchar. This is instrumentation data. Since we will be uploading those files directly to a SQL Server ...
0
votes
1answer
99 views

mysql string to date

I got error when i try to change string to date. mysql> select STR_TO_DATE('03.05.2010 00:00','%d.%m.%Y %h:%i'); +--------------------------------------------------+ | STR_TO_DATE('03.05.2010 00:...
22
votes
1answer
3k views

What date/time literal formats are LANGUAGE and DATEFORMAT safe?

It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language: yyyyMMdd ...
2
votes
1answer
1k views

Conversion from varchar2 to timestamp

In a oracle table column, there is a varchar2 column which contains value '2017-02-14T18:04:34.988+00:00' I need to convert it to timestamp. I am using TO_TIMESTAMP() to convert but I am not able to ...
0
votes
1answer
258 views

How to select and show all the tickets from last calendar year?

It seems SQL calls varies a lot, and cannot find how to do this in Firebird, where SQL dialect is 3. There's a table called TICKETS. This table has INSERT_TIME which is TIMESTAMP. How do I select all ...
0
votes
2answers
403 views

MySQL Union All Causing date field to lose format

I have 3 subqueries that work fine individually. I am attempting to join them with Union All. The query runs and gives the correct number of results but the format of the date field is messed up; for ...
1
vote
0answers
186 views

Oracle 11.2 index on VARCHAR vs index on DATE

I have a table with 15,339,375 records and it has an index on a date field that's converted to a VARCHAR(5) and stored as "MMYDD". It has an index on the field. The records in the table are all from ...
0
votes
1answer
7k views

Incorrect datetime value for str_to_date

I'm trying to copy a varchar column which contains dates in the format YYYY-MM-DD to a date column using str_to_date. My query is: UPDATE subscribers SET srtdt = STR_TO_DATE(StartDate, '%Y%m%d%'); ...
0
votes
2answers
2k views

Commands creating format files to import txt file into sql server

I'm trying to create a format file using bcp utility following the instructions on this page: Using a format file to bulk import data https://technet.microsoft.com/en-us/library/ms178129(v=sql.105)....
1
vote
1answer
172 views

Why do those Update statements fail using a datetime format?

Given: Query #1: begin tran UPDATE [dbo].[t] SET [mydatetime]='2011-12-25 07:00:00.000'; rollback Query #2: (changing month/day) begin tran UPDATE [dbo].[t] SET [mydatetime]='2011-25-12 07:00:00....
1
vote
1answer
115 views

SQLLite query datatime divided into week of the year, and month of the year

In my database I have two tables: type_of_activity CREATE TABLE IF NOT EXISTS type_of_activity(id_type INTEGER PRIMARY KEY, name VARCHAR(20)) activity CREATE TABLE IF NOT EXISTS activity( ...
0
votes
1answer
1k views

How to selecting data where the date is with the next month or later

I want to display all data where the the expiry date is within next month or later, here is my code: <cfquery name="ExpiryDate"> SELECT * FROM Date WHERE Expiry >= ...
1
vote
2answers
684 views

number of seconds to years-months-weeks-days-HH:MM-SS

I am running this all the time to get HH:MM:SS from a number of seconds: DECLARE @s INT SELECT @s = 23251 SELECT @s , CONVERT(TIME, DATEADD(SECOND, @s, 0)); when I want to add days to the ...
5
votes
3answers
10k views

Force SQL Server to parse “DDMMYYYY”

I'm developing ETL from a file where dates are expressed in the format "DDMMYYYY", example "31012016". I'd like to grab this string and pass it directly to the SQL text where data will be inserted. I ...
0
votes
1answer
27 views

Concatenating time values

I have a problem. In my access database I have two fields with DateTime. I use them for when a meeting starts and ends. I would like to concatenate them for a report, but 09.00 ends up as 9.0 so my ...
1
vote
1answer
393 views

Convert rfc822 to either a GMT DATETIME or Unix timestamp in SQL Server

I am trying to find a way to convert an rfc822 string date (with timezone) to either a GMT value of data type DATETIME or a unix timestamp of data type INT. For example: Mon, 15 Aug 2016 11:36:36 UTC ...
1
vote
1answer
8k views

How can I correctly use the timezone format specifier in a TO_DATE call in Oracle

I am trying to obtain a DATE object with the following statement: TO_DATE('1970-01-01 00:00:00 UTC','YYYY-MM-DD HH24:MI:SS TZR') and TO_DATE('1970-01-01 00:00:00 UTC','YYYY-MM-DD TZH:TZM:SS TZR') ...
0
votes
1answer
262 views

How to pass, specific day month and year in the below query?

I have registration according to the data in a table and I want to show the whole month, I found the easy solution with below query but I do not know how can I pass specific day, month and year in ...
-1
votes
1answer
21k views

I need to change the format of 'date' from yyyy-dd-mm to mm-dd-yyyy [closed]

My dilema, I was requested to populate a table of sample data and I completed the command to add a date. I entered '07-07-2016' as sample data. When running a query the "date_ordered" field came ...
1
vote
2answers
4k views

Change all varchar date formats with a single query

|date(varchar) | |--------------| |2016-02-16 | | -------------| |03-12-2015 | |--------------| |07-07-2007 | |--------------| |2016-03-14 | Dates are stored in different formats and type ...
0
votes
2answers
120 views

Convert Function

I am working on converting a few dates to make them easier to read and when I call upon the convert function to do so, they simply don't convert. When I run the queries they look the same. The code is ...
0
votes
1answer
192 views

Change date precision values

Exposition: I have an oracle database table with date format as 'mm/dd/yyyy' and an unique constraint that contains the date column. My issue is that I can't seem to find a method to change the ...
12
votes
5answers
1k views

Convert a date range to an interval description

A requirement in a recent project was to report when a resource would be fully consumed. As well as the exhaustion calendar date I was asked to show the remaining time in English-like format, ...
16
votes
6answers
11k views

Determine 3rd Friday of each month

I need to determine the dates which are the "3rd Friday of each month" for a date range of "1.1.1996 - 30.8.2014" in SQL Server. I expect I should use a combination of DENSE_RANK() and PARTITION BY() ...
2
votes
1answer
153 views

Two Databases on same server handle date differently

I have two databases on the same server, one is a backup of the other from a few weeks ago. I have a third party application that has started misbehaving. It logs the LastLoginDate of a particular ...
1
vote
1answer
935 views

Having performance issue for an extract date without time to compare with sysdate,

I need to get all the rows where its 'time' field is equal to sysdate. The thing is I just want to compare dd/mm/yyyy and as my fields are in the date format I'm using select * from myTable where ...