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 below query? SELECT AAA.date_field, IFNULL(BBB.val,0) val FROM ( SELECT date_field FROM ( SELECT MAKEDATE(YEAR('2014-02-06'),1) + INTERVAL (MONTH('2014-02-06')-1) MONTH + INTERVAL daynum DAY date_field FROM ( SELECT t*10+u daynum FROM (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A, (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum ) AA ) AA WHERE MONTH(date_field) = MONTH('2014-02-06') ) AAA LEFT JOIN (SELECT date_field,val FROM school) BBB USING (date_field) Can you please help me how can I pass the day month and year in the above query?

I have a problem with T-SQL that is out of my control. Their T-SQL is failing to work with a date that was cast to **VARCHAR(10)**. They are expecting to get the default yyyy-mm-dd (the documented default style for 'date' - 21) Most of the time it works ok but it fails sometimes. I've found that it fails when the cast returns 'mmm dd yyyy' format - i.e Jun 21 2022. The **VARCHAR(10)** makes the result of the cast unusable as it truncates to 'mmm dd yyy' - i.e Jun 21 202. After some mucking around I found the following strange behaviour SELECT CONVERT(VARCHAR, CAST(GETDATE() as date)) FROM Invoices where InvoiceNumber > -1000000 Result: Jun 12 2025 (x50,000) SELECT TOP 1CONVERT(VARCHAR, CAST(GETDATE() as date)) FROM Invoices where InvoiceNumber > -1000000 Result: 2025-06-12 (x1) SELECT CONVERT(VARCHAR, CAST(GETDATE() as date)) FROM Invoices Result: 2025-06-12 (x50,000) It's hard to know what other information to provide as these results are so strange. Select @@LANGUAGE returns British. SQL2019, 15.0.4410.1 (november)

I have a RHEL linux server where I have installed mysql percona and set the auditing. The Queries received in the audit record has timestamp in below format. {"audit_record":{"name":"Query","record":"179295580_2024-10-03T10:56:38","timestamp":"2024-10-03T10:57:54 UTC","command_class":"drop_table","connection_id":"2","status":0,"sqltext":"drop table DPT","user":"abc[abc] @ localhost []","host":"localhost","os_user":"","ip":"","db":""}} In the above record , timestamp has UTC. ""timestamp":"2024-10-03T10:57:54 UTC"," And I want it in this format: "timestamp":"2024-10-03T10:57:54Z". Basically I want to replace UTC with Z. I tried changing the timezone of the server , but its no help. Please help me with this. Thankyou

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 there between that day and the current date. I tried doing:

SELECT DATE_PART('day', AGE(CURRENT_TIMESTAMP, '2005-05-25 06:04:08'::timestamp )) AS days;

But for some reason this returns 11, it's not taking into account the difference in years. How can I solve this?

I have a query that I want to get data from the past 5 quarters, but it doesn't work the way it should: select to_char(eventdate,'YYYY') || ' Q-' || to_char(eventdate, ' Q'), ... from ... where eventdate between (sysdate - 458) and sysdate It doesn't do what I want. How do I make sure I get all rows for the current quarter and the last 4, no matter what day of the quarter it is?

I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and all the rest are simply date. I can't change this because I don't have admin access, and in fact I'm only querying the view. Postgres is fairly new to me, so I don't really understand how the dates and times work with each other. When I try and query the server with timestamp with time zone by casting the timestamp as a date: ... WHERE ( status_code = '30000' OR status_code = '30005' ) AND CAST(expiration AS DATE) > now() It works, but using the same query on the servers where expiration is already a date fails: > [Err] ERROR: invalid input syntax for type date: "No End Date" Any help would be appreciated, I'd really rather not hard code an exception for this one DB server.

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 diff function in the where clause. For example, find out last 30 days of data by filtering column ABCD: ABCD >= DATEADD(month, -1, GETDATE())) \* Note I was able to convert the varchar into datetime with the below code but cant figure it out how to use it in the where clause and use DateDiff fuction.

CONVERT(datetime,
        (
         LEFT(ABCD,4) + SUBSTRING(ABCD,5,2) + SUBSTRING(ABCD,7,2) 
         + ' ' 
         + SUBSTRING(ABCD,9,2) + ':'+ SUBSTRING(ABCD,11,2) + ':'+ SUBSTRING(ABCD,13,2)), 
         101) AS Datetime

The 'CD' can be discarded. All we need is to use the field ABCD in the where clause along with datediff function to find last 30 days data.

How can I set my database to see the 'date' as "MDY" without running: SET datestyle = "ISO, MDY"; every-time I'm trying to access it? I'm using Postgresql version 9.1, Ubuntu 12.04. My system locale, at the time of the database installation was set to en_CA.utf8 and I recently changed it to en_US.utf8. show lc_CTYPE returns: --> "en_CA.UTF-8" but show LC_CoLLATE returns: --> "en_CA.UTF-8"

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, so it would return me another column in English, Japanese, so on... My problem is that I have to set the locale BEFORE running the select command.

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 the tickets which has insert time year = 2016?

I'm trying to copy a VARCHAR column into a DATE column with the following:

memberScores SET tempDate = STR_TO_DATE(eventDate,%d-%m-%Y) WHERE scoreID < 68010;

The error message that I receive is this:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%d-%m-%Y) WHERE scoreID < 68010' at line 1

The VARCHAR data in the eventDate column is in the format of 1-15-2010 I've tried several variations on this, such as the following:

memberScores SET tempDate = STR_TO_DATE(eventDate,%d-%m-%Y) WHERE scoreID < 68010;

INTO memberScores.tempDate SELECT STR_TO_DATE(memberScores.eventDate,%d-%m-%Y) WHERE memberScores.scoreID < 68010;

I did find https://dba.stackexchange.com/questions/202627/incorrect-value-for-str-to-date and I initially thought the answer there was relevant because I'm using Alma Linux (similar to Centos) and the STR_TO_DATE function where the time component was zero... but the fix there didn't work. MySQL version is 8.0 on Alma Linux 8.7.0 Any ideas? EDIT - Added the exact error message.

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 is the following:

create table test diststyle all 
as	(
		select 
				psid
				,count(case when year_month = convert(bigint, to_char(date_trunc('month', current_date) - interval '14 month', 'yyyymm')) then psid else null end) as to_char(date_trunc('month', current_date - interval '14 month'), 'yyyymm') **--column I want**
				,count(case when year_month = to_char(date_trunc('month', current_date) - interval '13 month', 'yyyymm') then psid else null end) as "202202" --hardcode
		from 	tb_app_opened_globoplay_redu
		group by psid
	);

Can you please help me with that?

I went looking for a Rust crate (on lib.rs) that implements DateTime and found chrono.rs. However, I have two issues. 1. I cannot find a function that takes a string representing an SQL date and converts that into the external representation. I found a variety of conversion functions but they all require format strings specifying how the date is laid out in the string, but I don't know what the format specifier should be. The parser for SQL is simply handing me this date as a string and I don't know how the string is formatted. (The code I am parsing is one of the tpch queries, so it includes both a date and an interval, which I'm hoping can be converted to a "duration".) 2. The backend code which I am connecting to expects the date to be represented as a 64-bit unsigned integer. Is there a specific representation that is used for such? (Note, I asked on the Rust discord server, but only got the answer that the task should be "easy".)

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): Incorrect datetime value: '09:22' for function str_to_date I find that a similar statement, SELECT STR_TO_DATE("09:22", "%H:%i") gives me a NULL when I run it from a HeidiSQL client. I'm sure this syntax used to work and I'm not aware of having changed any settings, can you suggest what I've done wrong? Edit: Just remembered I have made a recent change, I installed a Wordpress database on the same server, might that have had some affect? Here's what get running on the server: mysql> select str_to_date("09:22", "%H:%i"); +-------------------------------+ | str_to_date("09:22", "%H:%i") | +-------------------------------+ | NULL | +-------------------------------+ 1 row in set, 1 warning (0.00 sec) Warning (Code 1411): Incorrect datetime value: '09:22' for function str_to_date

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 convert(datetime,'08/15/2022',103)

I get an error: > The conversion of a varchar data type to a datetime data type resulted in an out-of-range value When I change it to:

select convert(datetime,'08/15/2022',101)

...it works. I know UK English does not use mm/dd/yyyy, it was a test I was doing to see which default language SQL Server was using, so I could identify an issue in a website. I am looking therefore to set the default language in SQL to UK English instead of US English. I did use backup/restore migration. It seems SQL Server 2019 interprets SQL queries slightly differently from SQL Server 2008. I have no access to the coded webpages, so I cannot make any changes to the ASP coding. Is there *any* way I can run some T-SQL query to change the way SQL Server 2019 sees the date and force it to use UK English? The server region settings are set correctly, to English South Africa (the same as UK English), so I am not sure where else I can make any changes so that we get the correct date and time format. If I change the default language of the user in question, will this achieve my goal and do I need to restart SQL server in order to implement the change? The user I am referring to is the user set in SQL to connect to the database to run queries, set in the ODBC Driver settings. This is in relation to a website we are running, which I cannot change coding for as I know nothing about ASP. This is the code causing the issue:

-vb
sSQL = "UPDATE VR_UPLOAD SET clnt_status = 'O', clnt_date_send = '" & 
Left(strDate, 2) & "-" & Mid(strDate, 5, 2) & "-" & Right(strDate, 4) & " " & 
Left(strTime, 2) & ":" & Mid(strTime, 3, 2) & ":" & Right(strTime, 2) & ".000', 
CLNT_OUTFILE = '" & strFileName & 
"' where clnt_validated = '' and clnt_status = 'i'"
oConn.Execute(sSQL)

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') and received > Incorrect datetime value: 'Sun Jun 20 00:40:27 IST 2021' for function > str_to_date error. I also tried date_format('Sun Jun 20 00:40:27 IST 2021','%d/%m/%Y %T') and received > Incorrect datetime value: 'Sun Jun 20 00:40:27 IST 2021 Can anyone guide me to fix it? PS: I'm using prepared statement, executeBatch() to insert data to table.

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 the date data-types (date, smalldate, datetime) are presented as datetime2(7) even datetime2 with less ms presicions. I tried to execute the following query: SELECT CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(2)) AS 'time' ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 'smalldatetime' ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2' And I displayed the following table: Is there any management settings that stricts the date conversion or alternitavly, is there any more powerful conversion functions to display and store data in the correct data types?

I've dealt with MS SQL Server datetime types for a long time but never thought why the following is happening: 1. I query a table that contains a smalldatetime column. This smalldatetime is always returned in the format yyyy-MM-dd hh:mm:ss 2. Now I write a different query on which I want to apply a smalldatetime filter in the WHERE clause, something like WHERE TimeStamp >= 'yyyy-MM-dd hh:mm:ss' 3. SQL Server retrieves an error and tells me that was not possible to convert that nvarchar to a valid smalldatetime It appears that it only works if I change the specified format and I write it using the european format, like WHERE TimeStamp >= 'dd-MM-yyyy hh:mm:ss'. Why is SQL Server showing me the dates in a format that is not covertable or valid when applied back to itself? I don't have any problem in changing the date format when writing queries, but I want to play with these dates at an application level (Java-JDBC app) and I don't want to be applying date format changes all the time... Could anyone explain me why this is happening and if there is any way to solve it at a DB level? Thanks!! Edit: Please see the screenshot of the error in Management Studio below.

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 column, I'll get an exception:

Db error: Incorrect datetime value: 2016-02-10T12:00:00.000Z for column my_db.my_table.dt_column

I've tried to replace Z with '+00:00' -- to no avail, the error remained the same. What's incorrect about it? Should I tweak the data type of my column somehow? Or should I preprocess the value itself? How?

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 import it with : copy sample(quant,year) from 'data.csv' (DELIMITER ',', FORMAT 'csv', HEADER); It encounter an error: ERROR: invalid input syntax for type date: "2019" CONTEXT: COPY sample, line 2, column year: "2019" It is the only way to set year's type as int? create table sample( quant int,year int); How can import the data.csv without rewrite year's type?