Ggtcf

How do I exit BASH while loop using modulus operator?

Could neural networks be considered metaheuristics?

What is an equivalently powerful replacement spell for Yuan-Ti's Suggestion spell?

What do you call someone who asks many questions?

Bullying boss launched a smear campaign and made me unemployable

Could the museum Saturn V's be refitted for one more flight?

How to install cross-compiler on Ubuntu 18.04?

Was the Stack Exchange "Happy April Fools" page fitting with the '90's code?

In the UK, is it possible to get a referendum by a court decision?

Does Dispel Magic work on Tiny Hut?

Forgetting the musical notes while performing in concert

Pact of Blade Warlock with Dancing Blade

How to travel to Japan while expressing milk?

Do Iron Man suits sport waste management systems?

How badly should I try to prevent a user from XSSing themselves?

How can saying a song's name be a copyright violation?

Label inside tikzcd square

Avoiding the "not like other girls" trope?

How to remove border from elements in the last row?

Notepad++ delete until colon for every line with replace all

Is it possible to map the firing of neurons in the human brain so as to stimulate artificial memories in someone else?

Sums of two squares in arithmetic progressions

Do creatures with a speed 0ft., fly 30ft. (hover) ever touch the ground?

files created then deleted at every second in tmp directory

SQL query to bring back list of servers from specific date range

Get month and year from a datetime in SQL Server 2005How to return only the Date from a SQL Server DateTime datatypeHow to concatenate text from multiple rows into a single text string in SQL server?SQL Server: Query fast, but slow from procedureHow can I list all foreign keys referencing a given table in SQL Server?How can I get column names from a table in SQL Server?How do I UPDATE from a SELECT in SQL Server?SQL Server: group dates by rangesConvert Oracle Datetime format query to MS SQL Server FormatI have a date in the format of Mar-19. I need to select range for the entire month in SQL Server query

0

I'm trying to make a query with SQL Server Management Studio 2017 that brings back a count of all the servers with a projected migration date of this year. I have one query made now, but it's still bringing back some servers with dates from years before.

SELECT MONTH(Projected) as [Month], count(*) as [Total]
FROM dbo.tFake
WHERE Projected >='01/01/2019' AND Projected <='12/31/2019
GROUP BY Month(Projected)
ORDER BY [Month]

Date format is mm/dd/yyyy btw. How can I get this query to bring back just servers that are projected for the year 2019?

sql sql-server date-formatting

share|improve this question

edited Mar 9 at 18:41

Onur A.

2,67131733

asked Mar 8 at 20:39

ScaredInternScaredIntern

31

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to Stack Overflow! You have an unclosed quotation mark, although I suspect that's a typo, since the code as written won't run. Please post a data sample that returns servers outside your date range. You could also try "Where Year(Projected) = 2019" rather than two date comparisons.
  
  – Brian
  Mar 8 at 20:43
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  What do you mean the date format is mm/dd/yyyy? Date & time data types don't have a format in SQL Server. You aren't storing dates storing dates as a varchar are you? a varchar is not a date, and this would explain why you're getting the wrong data; you've chosen the wrong data type. For example, with a varchar the statement '12/01/2000' > '01/01/2019' is true. Why? Because varchar's are compared using their characters from left to right. As '1' > '0' this means that '12/01/2000' > '01/01/2019'.
  
  – Larnu
  Mar 8 at 20:43
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  sqlblog.org/2009/10/12/…
  
  – Sean Lange
  Mar 8 at 20:52
  

  
  
  
  

add a comment | 

0

I'm trying to make a query with SQL Server Management Studio 2017 that brings back a count of all the servers with a projected migration date of this year. I have one query made now, but it's still bringing back some servers with dates from years before.

SELECT MONTH(Projected) as [Month], count(*) as [Total]
FROM dbo.tFake
WHERE Projected >='01/01/2019' AND Projected <='12/31/2019
GROUP BY Month(Projected)
ORDER BY [Month]

Date format is mm/dd/yyyy btw. How can I get this query to bring back just servers that are projected for the year 2019?

sql sql-server date-formatting

share|improve this question

edited Mar 9 at 18:41

Onur A.

2,67131733

asked Mar 8 at 20:39

ScaredInternScaredIntern

31

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to Stack Overflow! You have an unclosed quotation mark, although I suspect that's a typo, since the code as written won't run. Please post a data sample that returns servers outside your date range. You could also try "Where Year(Projected) = 2019" rather than two date comparisons.
  
  – Brian
  Mar 8 at 20:43
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  What do you mean the date format is mm/dd/yyyy? Date & time data types don't have a format in SQL Server. You aren't storing dates storing dates as a varchar are you? a varchar is not a date, and this would explain why you're getting the wrong data; you've chosen the wrong data type. For example, with a varchar the statement '12/01/2000' > '01/01/2019' is true. Why? Because varchar's are compared using their characters from left to right. As '1' > '0' this means that '12/01/2000' > '01/01/2019'.
  
  – Larnu
  Mar 8 at 20:43
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  sqlblog.org/2009/10/12/…
  
  – Sean Lange
  Mar 8 at 20:52
  

  
  
  
  

add a comment | 

I'm trying to make a query with SQL Server Management Studio 2017 that brings back a count of all the servers with a projected migration date of this year. I have one query made now, but it's still bringing back some servers with dates from years before.

SELECT MONTH(Projected) as [Month], count(*) as [Total]
FROM dbo.tFake
WHERE Projected >='01/01/2019' AND Projected <='12/31/2019
GROUP BY Month(Projected)
ORDER BY [Month]

Date format is mm/dd/yyyy btw. How can I get this query to bring back just servers that are projected for the year 2019?

sql sql-server date-formatting

share|improve this question

edited Mar 9 at 18:41

Onur A.

2,67131733

asked Mar 8 at 20:39

ScaredInternScaredIntern

31

sql sql-server date-formatting

sql sql-server date-formatting

share|improve this question

edited Mar 9 at 18:41

Onur A.

2,67131733

asked Mar 8 at 20:39

ScaredInternScaredIntern

31

share|improve this question

edited Mar 9 at 18:41

Onur A.

2,67131733

asked Mar 8 at 20:39

ScaredInternScaredIntern

31

edited Mar 9 at 18:41

Onur A.

2,67131733

edited Mar 9 at 18:41

Onur A.

2,67131733

asked Mar 8 at 20:39

ScaredInternScaredIntern

31

asked Mar 8 at 20:39

ScaredInternScaredIntern

31

1 Answer
1

active

oldest

votes

1

Going by the assumption that your data type is wrong, the first step is to fix that.
Note, I am assuming that your data only contains dates, and not time (which your query implies). Firstly, you'll need to change the value of all your rows to an convertible value, we'll go with the ISO format yyyyMMdd:

UPDATE dbo.tFake
SET Projected = CONVERT(varchar(8),CONVERT(date,Projected,101),112);

Now that all the rows are a literal string in the format yyyyMMdd we can alter the column:

ALTER TABLE dbo.tFake ALTER COLUMN Projected date; 

Now, we can run your query again, but now your data type is correct, you won't have the problem:

SELECT MONTH(Projected) as [Month], count(*) as [Total]
FROM dbo.tFake
WHERE Projected >= '20190101' AND Project < '20200101' --I prefer the >= and < method. This is especially import with date & time data types
GROUP BY Month(Projected)
ORDER BY [Month];

Notice the literal strings I passed are also in the yyyyMMdd format. If you must pass a literal string in the format MMddyyyy you can wrap in a CONVERT with the style code 101: CONVERT(date,'12/31/2019',101). 101 means the US style date (CAST and CONVERT (Transact-SQL).

Remember, this solution assumes you have date only values, and not date and time values. If you do (have date and time values) you'll want to use an appropriate date and time data type and use the ISO8601 style, instead of the ISO style.

share|improve this answer

answered Mar 8 at 21:28

LarnuLarnu

22.4k51933

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This definitely helped! I was able to get the query bring back exactly what I wanted. Thanks!
  
  – ScaredIntern
  Mar 14 at 22:28
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You're welcome @ScaredIntern . Please do mark the answer as the solution if it did solve your problem, so that future readers know the answer helped. Thanks.
  
  – Larnu
  Mar 14 at 23:01
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55070679%2fsql-query-to-bring-back-list-of-servers-from-specific-date-range%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Going by the assumption that your data type is wrong, the first step is to fix that.
Note, I am assuming that your data only contains dates, and not time (which your query implies). Firstly, you'll need to change the value of all your rows to an convertible value, we'll go with the ISO format yyyyMMdd:

UPDATE dbo.tFake
SET Projected = CONVERT(varchar(8),CONVERT(date,Projected,101),112);

Now that all the rows are a literal string in the format yyyyMMdd we can alter the column:

ALTER TABLE dbo.tFake ALTER COLUMN Projected date; 

Now, we can run your query again, but now your data type is correct, you won't have the problem:

SELECT MONTH(Projected) as [Month], count(*) as [Total]
FROM dbo.tFake
WHERE Projected >= '20190101' AND Project < '20200101' --I prefer the >= and < method. This is especially import with date & time data types
GROUP BY Month(Projected)
ORDER BY [Month];

Notice the literal strings I passed are also in the yyyyMMdd format. If you must pass a literal string in the format MMddyyyy you can wrap in a CONVERT with the style code 101: CONVERT(date,'12/31/2019',101). 101 means the US style date (CAST and CONVERT (Transact-SQL).

Remember, this solution assumes you have date only values, and not date and time values. If you do (have date and time values) you'll want to use an appropriate date and time data type and use the ISO8601 style, instead of the ISO style.

share|improve this answer

answered Mar 8 at 21:28

LarnuLarnu

22.4k51933

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This definitely helped! I was able to get the query bring back exactly what I wanted. Thanks!
  
  – ScaredIntern
  Mar 14 at 22:28
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You're welcome @ScaredIntern . Please do mark the answer as the solution if it did solve your problem, so that future readers know the answer helped. Thanks.
  
  – Larnu
  Mar 14 at 23:01
  

  
  
  
  

add a comment | 

1

Going by the assumption that your data type is wrong, the first step is to fix that.
Note, I am assuming that your data only contains dates, and not time (which your query implies). Firstly, you'll need to change the value of all your rows to an convertible value, we'll go with the ISO format yyyyMMdd:

UPDATE dbo.tFake
SET Projected = CONVERT(varchar(8),CONVERT(date,Projected,101),112);

Now that all the rows are a literal string in the format yyyyMMdd we can alter the column:

ALTER TABLE dbo.tFake ALTER COLUMN Projected date; 

Now, we can run your query again, but now your data type is correct, you won't have the problem:

SELECT MONTH(Projected) as [Month], count(*) as [Total]
FROM dbo.tFake
WHERE Projected >= '20190101' AND Project < '20200101' --I prefer the >= and < method. This is especially import with date & time data types
GROUP BY Month(Projected)
ORDER BY [Month];

Notice the literal strings I passed are also in the yyyyMMdd format. If you must pass a literal string in the format MMddyyyy you can wrap in a CONVERT with the style code 101: CONVERT(date,'12/31/2019',101). 101 means the US style date (CAST and CONVERT (Transact-SQL).

Remember, this solution assumes you have date only values, and not date and time values. If you do (have date and time values) you'll want to use an appropriate date and time data type and use the ISO8601 style, instead of the ISO style.

share|improve this answer

answered Mar 8 at 21:28

LarnuLarnu

22.4k51933

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This definitely helped! I was able to get the query bring back exactly what I wanted. Thanks!
  
  – ScaredIntern
  Mar 14 at 22:28
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You're welcome @ScaredIntern . Please do mark the answer as the solution if it did solve your problem, so that future readers know the answer helped. Thanks.
  
  – Larnu
  Mar 14 at 23:01
  

  
  
  
  

add a comment | 

Going by the assumption that your data type is wrong, the first step is to fix that.
Note, I am assuming that your data only contains dates, and not time (which your query implies). Firstly, you'll need to change the value of all your rows to an convertible value, we'll go with the ISO format yyyyMMdd:

UPDATE dbo.tFake
SET Projected = CONVERT(varchar(8),CONVERT(date,Projected,101),112);

Now that all the rows are a literal string in the format yyyyMMdd we can alter the column:

ALTER TABLE dbo.tFake ALTER COLUMN Projected date; 

Now, we can run your query again, but now your data type is correct, you won't have the problem:

SELECT MONTH(Projected) as [Month], count(*) as [Total]
FROM dbo.tFake
WHERE Projected >= '20190101' AND Project < '20200101' --I prefer the >= and < method. This is especially import with date & time data types
GROUP BY Month(Projected)
ORDER BY [Month];

Notice the literal strings I passed are also in the yyyyMMdd format. If you must pass a literal string in the format MMddyyyy you can wrap in a CONVERT with the style code 101: CONVERT(date,'12/31/2019',101). 101 means the US style date (CAST and CONVERT (Transact-SQL).

Remember, this solution assumes you have date only values, and not date and time values. If you do (have date and time values) you'll want to use an appropriate date and time data type and use the ISO8601 style, instead of the ISO style.

share|improve this answer

answered Mar 8 at 21:28

LarnuLarnu

22.4k51933

UPDATE dbo.tFake
SET Projected = CONVERT(varchar(8),CONVERT(date,Projected,101),112);

ALTER TABLE dbo.tFake ALTER COLUMN Projected date; 

SELECT MONTH(Projected) as [Month], count(*) as [Total]
FROM dbo.tFake
WHERE Projected >= '20190101' AND Project < '20200101' --I prefer the >= and < method. This is especially import with date & time data types
GROUP BY Month(Projected)
ORDER BY [Month];

share|improve this answer

answered Mar 8 at 21:28

LarnuLarnu

22.4k51933

answered Mar 8 at 21:28

LarnuLarnu

22.4k51933

answered Mar 8 at 21:28

LarnuLarnu

22.4k51933