Stack Overflow
Stack Overflow

Questions tagged [datepart]

DATEPART is a T-SQL function that converts a DATE type to INT according to provided format.

datepart(datepart,date_time_value) is a T-SQL function available in MS SQL Server and the SAP/Sybase family of database products.

SQL Server Syntax

SAP/Sybase Syntax

257 questions
2
votes
2 answers

JOIN on DATEPART month and year is causing extra rows

I have two tables that contain a date field. This date field is one of the JOIN causes that I would like to implement, but I only want to JOIN on the month and year, not the day. The # of records about triple when I attempt to do so. I'm guessing…
user3329160
  • 165
  • 2
  • 13
2
votes
0 answers

"Cannot be translated into a LINQ to Entities ...", but it should

This sould be able to be translated into a Linq to entities store expression, but it throws that it cant. for (int day = 1; day <= 7; day = day + 1) pairs.Add("[" + day + ", " + db.Messages.Count(m => SqlFunctions.DatePart("weekday", m.Date) ==…
sports
  • 7,851
  • 14
  • 72
  • 129
2
votes
2 answers

How to properly convert a date into a ISO-8601 Week number format in SQL Server?

ISO-8601 states that week numbers are to be formated as YYYY-W## - observe that the week number should be two digits as 01, 02, ... SELECT cast(DATEPART(YYYY, CreationDate) as varchar) + '-W' + cast(DATEPART(ISO_WEEK, GETDATE())` The…
sorin
  • 161,544
  • 178
  • 535
  • 806
2
votes
3 answers

How to Insert table column values: month & day from column datetype

I'm using Oracle 11g Schema I want to select the month and day from a date concatenate it and put it into a column. The syntax I have here makes sense but its throwing an error of.. INSERT INTO OB_SELECT_LST12_SPG WED VALUES…
SeattleGray
  • 95
  • 1
  • 3
  • 10
2
votes
1 answer

count distinct hour from datetime

I am working with sql database, and I have problem. It is about public transportation, and I have table 'Traffic accidents' and column 'Time when accident happened' (datetime). I need to count how many accidents happened in specific HOUR, to realize…
Urosh
  • 95
  • 1
  • 2
  • 10
1
vote
3 answers

Daily counts with TSQL?

I have a site where I record client metrics in a SQL Server 2008 db on every link clicked. I have already written the query to get the daily total clicks, however I want to find out how many times the user clicked within a given timespan (ie.…
ElHaix
  • 12,846
  • 27
  • 115
  • 203
1
vote
1 answer

DATEPART(MS,datetime) returns incorrect value in SQL Server 2016 databases

I'm running SQL Server 2016, and recently changed the compatibility level of one of our databases to SQL Server 2016 (130). After that, I noticed the DATEPART function returns the wrong millisecond value from a DATETIME data type, for any value…
mfarrow
  • 13
  • 3
1
vote
5 answers

Select records from start of month to current date

I'm trying to select records that were added to the database between the start of the current month and the current day - I more or less know how to get records from the current day, and within a specific time period - but how do I get it so it…
Hani Honey
  • 2,101
  • 11
  • 48
  • 76
1
vote
1 answer

DATEPART and DATEFIRST alternative for redshift

I'm trying to run SELECT DATEPART(week, date(date)), MIN(date) in Redshift with the start day being Saturday. I tried using different syntax for SET DATEFIRST but it seems like it's not supported (or I just can't find the correct syntax for…
testman
  • 13
  • 2
1
vote
2 answers

Understanding explicit type casts in postgresql

WHEN 'H' = 'E' THEN CAST(DATE_PART('Year', now()) AS CHAR(4))+ '0101' I want to get the current year and adding to this year the month and the day. When I run the query, I run into below error. Did anyone experience this before? SQL Error [42883]:…
carr
  • 11
  • 5
1
vote
1 answer

Splitting up Datetime field on the fly in T-SQL

This is a query I wrote that I thought might be what I wanted. I think by looking at it you can see what I'm trying to do: INSERT INTO tbTime SELECT DISTINCT DATEPART(yyyy, ed), DATEPART(mm, ed), DATEPART(dd, ed), EntireDate AS…
Logan Serman
  • 29,447
  • 27
  • 102
  • 141
1
vote
2 answers

datepart function not considering updated date

The datepart function, when applied with the parameter week, was showing results of updated rows as well, even though the date is not in the range of the given week: select INSTALLATION_DATE from {MyTABLE} where datepart(WEEK, INSTALLATION_DATE) =…
Rekha K
  • 11
  • 1
1
vote
1 answer

weeknumbers ms access query

I would like to get weeknumbers in ms access according to: https://www.kalender-365.nl/kalender-2021.html My expression: test: DatePart("ww",#01/01/2021#,2,2) This returns 1 instead of week 53. What can I do to return 53? syntax: DatePart(datepart,…
sndr
  • 23
  • 6
1
vote
2 answers

Tableau - Creating Overlapping Date Bins

I have a report that will be updated Monday through Friday and want to display a single metric [Productivity %] across several different date [In_Date] "bins" i.e. a generated date dimension that would bin my data according to the following…
A. Oli
  • 41
  • 1
  • 6
1
vote
1 answer

SQL trigger DATEPART is not declared

I am trying to create a trigger that prompts an error message if the current time is between 10pm and 6am. This is the code I wrote: CREATE OR REPLACE TRIGGER horarioModificar BEFORE UPDATE ON employees FOR EACH ROW DECLARE horaActual NUMBER:=…
Jordi Osuna Gonzalez
  • 13
  • 2
1 2 3
…
17 18