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.
139
questions
0
votes
2
answers
139
views
Convert varchar into datetime and use date diff on the where clause
I have a field ABCD stored as varchar(16) and the data stored as 20170509074744CD.
I am not versed with SQL Language. I will greatly appreciate any help converting the field ABCD into datetime and use ...
- 1,039
0
votes
1
answer
84
views
Error in STR_TO_DATE when referring to column [closed]
I'm trying to copy a VARCHAR column into a DATE column with the following:
UPDATE memberScores SET tempDate = STR_TO_DATE(eventDate,%d-%m-%Y) WHERE scoreID < 68010;
The error message that I receive ...
0
votes
1
answer
1k
views
Dynamic data used as column name in Amazon Redshift
I am trying to use a year-month dynamic function to use it as a column name in Amazon Redshift. I don't want to write a hardcode for that. The database tool I'm using is DBeaver Ultimate and the code ...
- 1
-1
votes
1
answer
2k
views
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value when running T-SQL query
Please note I am not a coder by any means, I am simply trying to assist with a migration from SQL Server 2008 R2 and 2008 to SQL Server 2022 and 2019.
When I execute the following T-SQL query:
select ...
- 13
0
votes
0
answers
242
views
How to convert correctly from datetime2 data-type to small date and to elliminate HH:mm:SS.000000 padding
I have created a table with a column with a date data type which correspond to a date data in yyy-MM-dd format.
In the database it presents it as if the data type is datetime2(7).
More generally all ...
0
votes
1
answer
663
views
Inserting a "date-time" into TIMESTAMP column -- "Incorrect datetime value"
I have a date-time data that comes in this format:
2016-02-10T12:00:00.000Z
I've created a MariaDb database and a column for that date of type TIMESTAMP
However, when inserting the data into the ...
- 47
3
votes
2
answers
3k
views
Convert Date format to insert into MySQL database
I'm receiving the date in 'Sun Jun 20 00:40:27 IST 2021' format. Which I need to insert to my MySQL database in datetime(6) format.
I used
STR_TO_DATE('Sun Jun 20 00:40:27 IST 2021','%d/%m/%Y %T')
...
- 33
-1
votes
1
answer
667
views
How to store date with only four digital format?
In postgresql the date format is yyyy-mm-dd by default.
create table sample(
quant int,year date);
The data file is stored data.csv in csv format such as:
quant,year
10,2019
12,2020
13,2021
Try to ...
- 284
1
vote
1
answer
3k
views
Removing leading zeros from month and day
Someone in a PostgreSQL chat room gave me this syntax. It is used in a VIEW that outputs an RSS feed link:
to_char(tips_chronic_pain_weekly_selection.start_date, 'YYYY/MM/DD'::text)
Example output: ...
- 165
2
votes
1
answer
473
views
Is there a Rust crate that implements the SQL date and interval types?
I went looking for a Rust crate (on lib.rs) that implements DateTime and found chrono.rs. However, I have two issues.
I cannot find a function that takes a string representing an SQL date and ...
- 141
0
votes
2
answers
141
views
Using ISO8601 Dates in Oracle
I’m pretty experienced with other SQLs (PostgreSQL, Microsoft Server, MySQL, SQLite), but not so in Oracle.
Is there any way to get Oracle to accept date literals in ISO8601 format. For example:
...
- 2,769
1
vote
1
answer
256
views
Very old date displaying incorrectly when unformatted
At my organization, people have been entering unknown dates as "11/11/1111". When I run a query with the date field, the value shows up as "11/04/1111". However, if I format it as ...
- 33
0
votes
1
answer
455
views
Given a table with date_time column, how to run a query using only the date
I have a DB with a table which has a column datetime Date_Time.
The format of this is for example: 2021-09-01 13:15:16.
I would like to set a query that is indipendent of the time but just consider ...
- 101
1
vote
1
answer
4k
views
Calculate days between to dates postgresql
I'm having a problem:
I'm trying to calculate days between two dates.
Let's say I have the date '2005-05-25 06:04:08' and I want to count how many days are from that day to the current date.
I tried ...
- 11
0
votes
1
answer
387
views
What exactly is the datatype input into a DATETIME value in MySQL
I am trying to get data from a PLC directly into a MySQL 8.0 database (using MySQL Workbench). I need to know what data type the input value is for a column set to DATETIME so I can send in the right ...
- 105
0
votes
1
answer
318
views
Better way of handling incorrect date format in a column with "char(10)" data type / TRY_CONVERT equivalent in PLSQL
I have a source table with below structure:
create table customer_info
(customer_num number,
birth_date char(10))
Unfortunately the birth_date column data type is char(10) instead of date. ...
- 1,412
2
votes
2
answers
176
views
Can I change MSSQL interpretation of date order system wide?
I use Laravel with PHP from https://laravel.com/
MSSQL is a driver and works perfectly - with th exception of dates.
For some reason the project has decided that the follwoing is a universal date ...
- 123
-1
votes
2
answers
120
views
casting custom string to date in SQL
I have a column with strings such as 2015-W02, meaning the second week of 2015. I would like to convert it to a date (e.g., corresponding to the Saturday of that week). It does not seem that CONVERT ...
0
votes
2
answers
67
views
Can somebody please tell what is the difference between these two date comparisons?
So I have 2 queries as follows:
f.DATE >= (GETDATE() - 3)
f.DATE >= CAST(CONVERT(VARCHAR(10),GETDATE() - 3,101) AS DATETIME)
I think they are the same but the second one gives me more rows ...
- 1
0
votes
1
answer
143
views
to_char(resolved_date-opened_date, 'hh24:mi')
I am using the below to return the hh:mm difference between two date ranges. How would I update this code to minus the hours by 5?
CURRENT CODE = to_char(resolved_datetime-opened_datetime, 'hh24:mi')
...
- 5
0
votes
1
answer
1k
views
Create table using select statement Truncated incorrect datetime value:
I have looked up different answers on similar question before posting it here.
So i have a table with user id and date column
I would like to store the results of select query to a temporary table.
...
- 3
2
votes
1
answer
44k
views
PostgreSQL: date/time field value out of range
Software:
Linux cjz-eshop1-p 5.4.0-33-generic #37-Ubuntu SMP Thu May 21 12:53:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg18.04+1)
I am getting errors due to ...
- 181
2
votes
2
answers
2k
views
How to add a day/night indicator to a timestamp column?
There is a timestamp column I use to indicate whether a row was created during day or night time. My code is the following, but for some reason I only get 'DAY' as outcome. Am I not formatting the ...
- 43
0
votes
1
answer
1k
views
Postgres - default date format when interpreting strings
What is the default data format that postgres uses when confronted with a character representation of a date?
These SQL statements all work, and the character string is always correctly interpreted ...
- 1,125
1
vote
2
answers
180
views
Is it safe to use an output (to_char) date format string of more than 22 characters (ORACLE 11)
The ORACLE SQL manual (Page 2-58 of the main 11.1 SQL Manual) states:
The total length of a datetime format model cannot exceed 22 characters.
However, I've been able to use a format string for ...
- 13
0
votes
1
answer
757
views
ORACLE DB literal does not match format string
I' am having this error:
ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as ...
-1
votes
1
answer
1k
views
DATEPART YEAR from 'yyyymmdd' Formatted Date
I'm working with a table that stores the date field as 'YYYYMMDD' and I need to filter the query by the YEAR, using the DATEPART from that same date field.
WHERE DATEPART(yy,'YYYYMMDD') = 2018
- 189
0
votes
0
answers
1k
views
How to filter date columns in Mysql database effectively?
I have a column called created_at and its datatype is (date).
Consider the following two SQL queries.
select * from my_table where created_at < '2019-11-05'
and the other one is
select * from ...
2
votes
1
answer
351
views
Unexpected behaviour for to_date() with week number and week day
I tried to convert a date string (year, week, day of week = 'YYYY-WW-D') into a date using the to_date() function.
I got these results (demo:db<>fiddle):
date string | to_date()
-----------------...
- 179
0
votes
1
answer
2k
views
MySql date range query not showing all records
I am working on mysql query right now. I write a query from fetch data from march 2018 to march 2019. There are more than 5000 records for this year. I write a query with date range from march 2018 to ...
1
vote
2
answers
684
views
Equivalent of Oracle RR date format in SQL Server
Having a bunch of SQL insert scripts generated in Oracle, I have the requirement to adjust them to be usable with T-SQL in Microsoft SQL Server.
The Oracle export has date formats like this:
to_date(...
- 887
0
votes
1
answer
64
views
Postgresql Date Function Returning Range In Readable Format
I am wondering if it is possible to use postgresql's date-time , concat and case functions to give one of the following results:
June 4th to 11th 2019 (where the 1st and 2nd date are within the same ...
- 165
0
votes
3
answers
837
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
-1
votes
1
answer
23
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 ...
- 1
0
votes
1
answer
83
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 ...
- 1,702
0
votes
0
answers
57
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 ...
- 1
-6
votes
1
answer
90
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
2
votes
2
answers
3k
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 ...
- 133
1
vote
1
answer
743
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 ...
- 43
1
vote
3
answers
2k
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,...
- 123
1
vote
0
answers
114
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")
# ...
- 131
-1
votes
2
answers
872
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 ...
- 533
5
votes
1
answer
1k
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 ...
- 53
1
vote
0
answers
2k
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 ...
- 121
1
vote
2
answers
13k
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): ...
- 13
1
vote
0
answers
2k
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): ...
- 513
1
vote
1
answer
2k
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-...
- 13
1
vote
1
answer
8k
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'::...
- 69
1
vote
1
answer
5k
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 (...
- 25
4
votes
1
answer
4k
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-...
- 143