All Questions
Tagged with datetime date-format
29 questions
29
votes
1
answer
8k
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 ...
- 182k
8
votes
2
answers
17k
views
Does SQL Server have a Date_Format function?
All I found is a list of predefined DateFormat that I can choose from, like this
As I remember in MySQL (and PostgeSQL too?) you can define your Date Format:
DATE_FORMAT(now(),'&m_%Y') --for ...
- 399
7
votes
2
answers
92k
views
How to get only month from MySQL date/time?
In my MySQL server, I have rows that have the date of my created content.
Example: 2013-03-17 02:53:47
For now, I am using:
$date = $fetch[thedaterownamegoeshere];
<?php echo $date; ?>
But ...
- 267
4
votes
2
answers
55k
views
Understanding datetime formats in SQL Server
I've dealt with MS SQL Server datetime types for a long time but never thought why the following is happening:
I query a table that contains a smalldatetime column. This smalldatetime is always ...
- 585
3
votes
2
answers
4k
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
3
votes
2
answers
8k
views
Converting string to date with timezone adjustment
I am attempting to convert stirngs to dates in mysql
Thu Oct 23 16:46:47 2014 +02:00
Im unsure how to handle the +02:00, The rest is simple enough
update logs
set date = str_to_date(date_raw, '%a ...
2
votes
1
answer
5k
views
How to remove leading zero from date [closed]
I have a query in which i have used the following
date_add(DATE_FORMAT(ss.week_startdate,'%y-%m-%d'),INTERVAL (tt.day_id- 1 )DAY) as date
Datatype of the field is datetime and the result date is ...
- 141
2
votes
2
answers
14k
views
Calculate time difference between a datetime and the current date & time
I would like to calculate the difference between a past datetime and the current datetime with the result in [hh]:mm format.
- 171
2
votes
1
answer
664
views
DATETIME2FROMPARTS alternatives for SQL Server 2008
I am trying to part a datetime value and build it together removing minutes, seconds and so on. I want the values to be hourly.
Usually this works on a SQL Server 2012 machine:
DATETIME2FROMPARTS( ...
- 167
2
votes
1
answer
643
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
1
vote
1
answer
9k
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
2k
views
Format timezone to +-#### instead of +-##
I am curious if there is a way to configure Postgres (9.1+) to format the TZ part of a "timestamp with time zone" as +-####, just like PHP's date.format method does using "O".
PHP Docs:
O | ...
- 231
1
vote
1
answer
921
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
2
answers
2k
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 ...
- 16.6k
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
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
688
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....
- 4,807
1
vote
1
answer
758
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
...
- 2,837
0
votes
1
answer
707
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
0
votes
2
answers
1k
views
Convert datetime to europe default without time
I have a datetime field that I need to convert into the format DDMONYY (27Aug12). I am currently using convert(nvarchar(30),DateToConvert,13), but that also spits out the time. How can I easily ...
- 3,068
0
votes
2
answers
422
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,063
0
votes
2
answers
579
views
WHERE clause converting INT to DATETIME
Looking for advice on how to match two differing column types:
I have two tables: RT-A and DA-B.
Under both tables there is a column named DeliveryDate, but the individual tables use differing ...
- 115
0
votes
1
answer
67
views
How do I drop the TIME portion from DATEADD?
I am trying to create some test data where each Employee has a random birthdate. I accomplished the randomizing part but it did not return result in the format that I want.. NOTE: the DateOfBirth ...
- 3
0
votes
1
answer
499
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
2
answers
75
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
2k
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 ...
- 445
0
votes
1
answer
6k
views
Date of birth saving to MYSQL DB with timestamp or date column type [closed]
i am using MYSQL database and would like to know which of this is a good practice.
way 1 - use column type in DB as timestamp. i.e will contain values like 2012-11-13 00:00:00 way 2 - use column as ...
- 103
0
votes
1
answer
366
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 ...
- 101
-1
votes
1
answer
8k
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