Styjun

Can you find the next number in the following series?

Existence of a certain set of 0/1-sequences without the Axiom of Choice

Do banks' profitability really suffer under low interest rates

Installing the original OS X version onto a Mac?

Replacing old plug-in 220V range with new hardwire 3-wire electric cooktop: remove outlet or add a plug?

Why was ramjet fuel used as hydraulic fluid during Saturn V checkout?

How could Tony Stark wield the Infinity Nano Gauntlet - at all?

Output with the same length always

What happened after the end of the Truman Show?

What should I do with the stock I own if I anticipate there will be a recession?

Eric Andre had a dream

Installing certbot - error - "nothing provides pyparsing"

Why do balloons get cold when they deflate?

Are there categories whose internal hom is somewhat 'exotic'?

What happens to thrust and drag in a straight and level flight?

Adding things to bunches of things vs multiplication

Did Michelle Obama have a staff of 23 people, while Melania has a staff of 4?

Is there a way to make the "o" keypress of other-window <C-x><C-o> repeatable?

Combinatorial Argument for Exponential and Logarithmic Function Being Inverse

Best model for precedence constraints within scheduling problem

Starships without computers?

Why is the name Bergson pronounced like Berksonne?

Can an ally use your Shadow Blade without it dissipating?

Is there a commercial liquid with refractive index greater than n=2?

How do i query a date time field with just a date

What is the culture neutral dateformat for SQL ServerDate conversion and culture: Difference between DATE and DATETIMETSQL what is the difference between using dd-mm-yyyy and yyyy-mm-ddHow to return only the Date from a SQL Server DateTime datatypeHow do you get a timestamp in JavaScript?How to get the current time in PythonInserting multiple rows in a single SQL query?Compare two dates with JavaScriptHow do I get the current date in JavaScript?How do I UPDATE from a SELECT in SQL Server?Daylight saving time and time zone best practicesHow to format a JavaScript dateWhy is subtracting these two times (in 1927) giving a strange result?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

I'm querying a SQL Server DB from VB.Net with below command but i cannot get it to select rows from a certain date as the column contains datetime values. It keeps saying

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I've tried CAST & CONVERT and various date functions but i cant get it to work

CmdText As String = "SELECT * FROM Races m WHERE (RaceStartTime BETWEEN '27/03/2019 00:00:01' AND '27/03/2019 23:59:59') AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"

I'm expecting rows on a given date but im receiving no rows at all. One line of data does have a date on the 27/03/2019 so this should be returned

sql-server tsql date datetime sql-server-express

share|improve this question

edited Mar 27 at 13:59

a_horse_with_no_name

329k5151 gold badges514514 silver badges607607 bronze badges

asked Mar 27 at 13:58

David LittleDavid Little

1111 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  use SET DATEFORMAT dmy; docs.microsoft.com/en-us/sql/t-sql/statements/…
  
  – Kemal AL GAZZAH
  Mar 27 at 14:29
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Rather than trying to get close to the end of a day, you should either Cast( RaceStartTime as Date ) to eliminate the time-of-day or use a half-open interval with the subsequent date, e.g. '2019-03-27' <= RaceStartTime and RaceStartTime < '2013-03-28' where the second comparison is <, not <=. PSA: ISO date format.
  
  – HABO
  Mar 27 at 14:45
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @HABO beware of datetime and yyyy-mm-dd and also this one
  
  – Zohar Peled
  Mar 27 at 14:59
  
  

  
  
  
  

add a comment | 

2

I'm querying a SQL Server DB from VB.Net with below command but i cannot get it to select rows from a certain date as the column contains datetime values. It keeps saying

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I've tried CAST & CONVERT and various date functions but i cant get it to work

CmdText As String = "SELECT * FROM Races m WHERE (RaceStartTime BETWEEN '27/03/2019 00:00:01' AND '27/03/2019 23:59:59') AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"

I'm expecting rows on a given date but im receiving no rows at all. One line of data does have a date on the 27/03/2019 so this should be returned

sql-server tsql date datetime sql-server-express

share|improve this question

edited Mar 27 at 13:59

a_horse_with_no_name

329k5151 gold badges514514 silver badges607607 bronze badges

asked Mar 27 at 13:58

David LittleDavid Little

1111 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  use SET DATEFORMAT dmy; docs.microsoft.com/en-us/sql/t-sql/statements/…
  
  – Kemal AL GAZZAH
  Mar 27 at 14:29
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Rather than trying to get close to the end of a day, you should either Cast( RaceStartTime as Date ) to eliminate the time-of-day or use a half-open interval with the subsequent date, e.g. '2019-03-27' <= RaceStartTime and RaceStartTime < '2013-03-28' where the second comparison is <, not <=. PSA: ISO date format.
  
  – HABO
  Mar 27 at 14:45
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @HABO beware of datetime and yyyy-mm-dd and also this one
  
  – Zohar Peled
  Mar 27 at 14:59
  
  

  
  
  
  

add a comment | 

I'm querying a SQL Server DB from VB.Net with below command but i cannot get it to select rows from a certain date as the column contains datetime values. It keeps saying

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I've tried CAST & CONVERT and various date functions but i cant get it to work

CmdText As String = "SELECT * FROM Races m WHERE (RaceStartTime BETWEEN '27/03/2019 00:00:01' AND '27/03/2019 23:59:59') AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"

I'm expecting rows on a given date but im receiving no rows at all. One line of data does have a date on the 27/03/2019 so this should be returned

sql-server tsql date datetime sql-server-express

share|improve this question

edited Mar 27 at 13:59

a_horse_with_no_name

329k5151 gold badges514514 silver badges607607 bronze badges

asked Mar 27 at 13:58

David LittleDavid Little

1111 bronze badge

CmdText As String = "SELECT * FROM Races m WHERE (RaceStartTime BETWEEN '27/03/2019 00:00:01' AND '27/03/2019 23:59:59') AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"

sql-server tsql date datetime sql-server-express

sql-server tsql date datetime sql-server-express

share|improve this question

edited Mar 27 at 13:59

a_horse_with_no_name

329k5151 gold badges514514 silver badges607607 bronze badges

asked Mar 27 at 13:58

David LittleDavid Little

1111 bronze badge

share|improve this question

edited Mar 27 at 13:59

a_horse_with_no_name

329k5151 gold badges514514 silver badges607607 bronze badges

asked Mar 27 at 13:58

David LittleDavid Little

1111 bronze badge

edited Mar 27 at 13:59

a_horse_with_no_name

329k5151 gold badges514514 silver badges607607 bronze badges

edited Mar 27 at 13:59

a_horse_with_no_name

329k5151 gold badges514514 silver badges607607 bronze badges

asked Mar 27 at 13:58

David LittleDavid Little

1111 bronze badge

asked Mar 27 at 13:58

David LittleDavid Little

1111 bronze badge

2 Answers
2

active

oldest

votes

0

The only reason you'd get this error is if your server is set to think of 27 as the month and 03 as the date instead of the other way around.

You should use locale-neutral strings like 'YYYYMMDD'

Tangential to the issue causing the error, another, less error-prone way, to get rows for a specific day is like this:

WHERE CONVERT(date, RaceStartTime) = '20190327'

share|improve this answer

edited Mar 27 at 14:08

answered Mar 27 at 14:03

Tab AllemanTab Alleman

28.7k66 gold badges2626 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ... Or even better, avoid using string representations of datetime altogether, if possible.
  
  – Zohar Peled
  Mar 27 at 14:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Appreciate the quick reply, can you advise how i would implement a locale-neutral string in the context of this query ?
  
  – David Little
  Mar 27 at 14:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  See the edit I just added.
  
  – Tab Alleman
  Mar 27 at 14:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry im new to SQL and it still doesn't work, it doesnt produce an exception but it doesnt return the one row im expecting it to Dim CmdText As String = "SELECT * FROM Races m WHERE CONVERT(date, RaceStartTime) = '20190327' AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"
  
  – David Little
  Mar 27 at 14:12
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Cast to date is sargable but is it a good idea? tl;dr; : No it's not.
  
  – Zohar Peled
  Mar 27 at 14:15
  

  
  
  
  

 | 
show 2 more comments

0

Did you get any error? also try without the Reactime?

CmdText As String = "SELECT * FROM Races m WHERE (RaceStartTime BETWEEN '27/Mar/2019 00:00:01' AND '27/Mar/2019 23:59:59') AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"

Can you try this query from Sql Server Management Studio first?
I Think the issue is might be for RaceTime??

share|improve this answer

answered Mar 27 at 14:17

Hasan MahmoodHasan Mahmood

96377 silver badges1010 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  sqlblog.org/2011/10/19/… And those date literals make my head spin, additionally they will exclude some rows because of the odd times you put on there.
  
  – Sean Lange
  Mar 27 at 14:20
  

  
  
  
  

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%2f55379047%2fhow-do-i-query-a-date-time-field-with-just-a-date%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

The only reason you'd get this error is if your server is set to think of 27 as the month and 03 as the date instead of the other way around.

You should use locale-neutral strings like 'YYYYMMDD'

Tangential to the issue causing the error, another, less error-prone way, to get rows for a specific day is like this:

WHERE CONVERT(date, RaceStartTime) = '20190327'

share|improve this answer

edited Mar 27 at 14:08

answered Mar 27 at 14:03

Tab AllemanTab Alleman

28.7k66 gold badges2626 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ... Or even better, avoid using string representations of datetime altogether, if possible.
  
  – Zohar Peled
  Mar 27 at 14:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Appreciate the quick reply, can you advise how i would implement a locale-neutral string in the context of this query ?
  
  – David Little
  Mar 27 at 14:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  See the edit I just added.
  
  – Tab Alleman
  Mar 27 at 14:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry im new to SQL and it still doesn't work, it doesnt produce an exception but it doesnt return the one row im expecting it to Dim CmdText As String = "SELECT * FROM Races m WHERE CONVERT(date, RaceStartTime) = '20190327' AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"
  
  – David Little
  Mar 27 at 14:12
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Cast to date is sargable but is it a good idea? tl;dr; : No it's not.
  
  – Zohar Peled
  Mar 27 at 14:15
  

  
  
  
  

 | 
show 2 more comments

0

The only reason you'd get this error is if your server is set to think of 27 as the month and 03 as the date instead of the other way around.

You should use locale-neutral strings like 'YYYYMMDD'

Tangential to the issue causing the error, another, less error-prone way, to get rows for a specific day is like this:

WHERE CONVERT(date, RaceStartTime) = '20190327'

share|improve this answer

edited Mar 27 at 14:08

answered Mar 27 at 14:03

Tab AllemanTab Alleman

28.7k66 gold badges2626 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ... Or even better, avoid using string representations of datetime altogether, if possible.
  
  – Zohar Peled
  Mar 27 at 14:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Appreciate the quick reply, can you advise how i would implement a locale-neutral string in the context of this query ?
  
  – David Little
  Mar 27 at 14:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  See the edit I just added.
  
  – Tab Alleman
  Mar 27 at 14:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry im new to SQL and it still doesn't work, it doesnt produce an exception but it doesnt return the one row im expecting it to Dim CmdText As String = "SELECT * FROM Races m WHERE CONVERT(date, RaceStartTime) = '20190327' AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"
  
  – David Little
  Mar 27 at 14:12
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Cast to date is sargable but is it a good idea? tl;dr; : No it's not.
  
  – Zohar Peled
  Mar 27 at 14:15
  

  
  
  
  

 | 
show 2 more comments

The only reason you'd get this error is if your server is set to think of 27 as the month and 03 as the date instead of the other way around.

You should use locale-neutral strings like 'YYYYMMDD'

Tangential to the issue causing the error, another, less error-prone way, to get rows for a specific day is like this:

WHERE CONVERT(date, RaceStartTime) = '20190327'

share|improve this answer

edited Mar 27 at 14:08

answered Mar 27 at 14:03

Tab AllemanTab Alleman

28.7k66 gold badges2626 silver badges4646 bronze badges

WHERE CONVERT(date, RaceStartTime) = '20190327'

share|improve this answer

edited Mar 27 at 14:08

answered Mar 27 at 14:03

Tab AllemanTab Alleman

28.7k66 gold badges2626 silver badges4646 bronze badges

answered Mar 27 at 14:03

Tab AllemanTab Alleman

28.7k66 gold badges2626 silver badges4646 bronze badges

answered Mar 27 at 14:03

Tab AllemanTab Alleman

28.7k66 gold badges2626 silver badges4646 bronze badges

0

Did you get any error? also try without the Reactime?

CmdText As String = "SELECT * FROM Races m WHERE (RaceStartTime BETWEEN '27/Mar/2019 00:00:01' AND '27/Mar/2019 23:59:59') AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"

Can you try this query from Sql Server Management Studio first?
I Think the issue is might be for RaceTime??

share|improve this answer

answered Mar 27 at 14:17

Hasan MahmoodHasan Mahmood

96377 silver badges1010 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  sqlblog.org/2011/10/19/… And those date literals make my head spin, additionally they will exclude some rows because of the odd times you put on there.
  
  – Sean Lange
  Mar 27 at 14:20
  

  
  
  
  

add a comment | 

0

Did you get any error? also try without the Reactime?

CmdText As String = "SELECT * FROM Races m WHERE (RaceStartTime BETWEEN '27/Mar/2019 00:00:01' AND '27/Mar/2019 23:59:59') AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"

Can you try this query from Sql Server Management Studio first?
I Think the issue is might be for RaceTime??

share|improve this answer

answered Mar 27 at 14:17

Hasan MahmoodHasan Mahmood

96377 silver badges1010 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  sqlblog.org/2011/10/19/… And those date literals make my head spin, additionally they will exclude some rows because of the odd times you put on there.
  
  – Sean Lange
  Mar 27 at 14:20
  

  
  
  
  

add a comment | 

Did you get any error? also try without the Reactime?

CmdText As String = "SELECT * FROM Races m WHERE (RaceStartTime BETWEEN '27/Mar/2019 00:00:01' AND '27/Mar/2019 23:59:59') AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"

Can you try this query from Sql Server Management Studio first?
I Think the issue is might be for RaceTime??

share|improve this answer

answered Mar 27 at 14:17

Hasan MahmoodHasan Mahmood

96377 silver badges1010 bronze badges

share|improve this answer

answered Mar 27 at 14:17

Hasan MahmoodHasan Mahmood

96377 silver badges1010 bronze badges

answered Mar 27 at 14:17

Hasan MahmoodHasan Mahmood

96377 silver badges1010 bronze badges

answered Mar 27 at 14:17

Hasan MahmoodHasan Mahmood

96377 silver badges1010 bronze badges