Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [datetime]

for questions specific to data types that contain both date and time parts.

391 questions
29
votes
1 answer

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 -- unseparated, date…
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
29
votes
6 answers

Preferred way to store DateTime

We can store Date and Time information in a couple of ways. What is the best approach for storing DateTime information? Storing Date and Time in 2 separate columns or one column using DateTime? Can you explain why that approach is better? (Link to…
Julian
  • 452
  • 1
  • 4
  • 11
22
votes
3 answers

Separate month and year columns, or date with day always set to 1?

I'm building a database with Postgres where there's going to be a lot of grouping of things by month and year, but never by the date. I could create integer month and year columns and use those. Or I could have a month_year column and always set…
David N. Welton
  • 323
  • 1
  • 2
  • 6
22
votes
4 answers

Why does my query search datetime not match?

select * from A where posted_date >= '2015-07-27 00:00:00.000' and posted_date <= '2015-07-27 23:59:59.999' But the result contains a record that has posted_date today: 2015-07-28. My database server is not in my country. What is the problem…
lecuong92
  • 293
  • 1
  • 3
  • 8
21
votes
6 answers

How to create a row for every day in a date range using a stored procedure?

I would like to create a stored procedure that will create a row in a table for every day in a given date range. The Stored Procedure accepts two inputs - A start date and end date of the date range desired by the user. So, let's say I have a table…
Rob V
  • 375
  • 2
  • 4
  • 8
21
votes
3 answers

Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA

First Words You can safely ignore the sections below (and including) JOINs: Starting Off if you just want to take a crack of the code. The background and results just serve as context. Please look at the edit history before 2015-10-06 if you want to…
mpag
  • 978
  • 7
  • 23
18
votes
2 answers

MySQL performance problem using indexed datetime column

I tried to solve the following problem for about one hour now and still didn't get any further with it. Okay, I have a table (MyISAM): +---------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key…
Robin Heller
  • 283
  • 1
  • 2
  • 7
18
votes
1 answer

How do I generate a date series in PostgreSQL?

If you're looking to generate a time series, see this question Let's say that I want to generate a series of dates between two dates. I see the function generate_series provides only Function Argument Type …
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
17
votes
3 answers

SQL Server Isnull Returning 1900-01-01 when field is null

The following piece of code is returning 1900-01-01 when the DOB field is null. I wanted (and expected) it to return an empty string ('') but it's not. How should I proceed to get my desired results? isnull(convert(date,DOB,1),'')
Juan Velez
  • 3,303
  • 19
  • 57
  • 75
17
votes
1 answer

MySQL 5.6 DateTime Incorrect datetime value: '2013-08-25T17:00:00+00:00' with Error Code 1292

I'm using MySQL 5.6 and I have a program that runs the following SQL statement against my database: UPDATE `m_table` SET `s_time` = '2013-08-25T17:00:00+00:00' WHERE id = '123' Unforutnately, I get the following error: Incorrect datetime value:…
Andrew
  • 273
  • 1
  • 2
  • 6
17
votes
5 answers

How to add 1 milliseconds to a datetime string?

Based on a select, I can return x rows like this: 1 2019-07-23 10:14:04.000 1 2019-07-23 10:14:11.000 2 2019-07-23 10:45:32.000 1 2019-07-23 10:45:33.000 We have all milliseconds with 0. Is there a way to add 1 by 1 milliseconds, so the…
Racer SQL
  • 7,546
  • 16
  • 77
  • 140
15
votes
2 answers

Incorrect comparison of datetime and datetime2

I know that it is not a good practice to have an implicit type convert. But this is really unexpected behavior when a lower value can suddenly become higher. declare @LastSelectedDate DATETIME = '2021-11-09 13:52:29.187' declare @LastSelectedDate_1…
Artashes Khachatryan
  • 1,533
  • 1
  • 12
  • 23
14
votes
2 answers

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and in books), a common solution seems to be to have…
Vesselin Obreshkov
  • 243
  • 2
  • 5
13
votes
4 answers

In the same query INSERT, can two VALUES being NOW() return a different time?

I met a query similar to INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(), NOW()) having column definitions | Created | datetime | | Updated | datetime | Will in this case MySQL set the NOW() value to the current time and return…
Déjà vu
  • 555
  • 2
  • 8
  • 19
12
votes
6 answers

Select COUNT of days between two dates except weekends

I am trying to get the number of days between two different dates, except the weekends. I don't know how to achieve this result.
pragati basa
  • 121
  • 1
  • 1
  • 3
1
2 3
…
25 26