Count issue/replace between!!!

Question

Count issue/replace between!!!

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

jcachado Mr or Mrs. 500 Points: 574 More actions · Answer 1

This is run with a string in VS 2012, the database table is a mdb (access):

Dim SQLStringtempprd123 As String = "SELECT CAST([create_dtim],'dd-mm-yyyy'),COUNT(*) AS tot FROM Back_ickpt_prd1 WHERE CAST([create_dtim],'dd-mm-yyyy') between '01-09-2016 00:00:01' and '05-01-2016 23:59:01' GROUP BY CAST([create_dtim],'dd-mm-yyyy') order by CAST([create_dtim],'dd-mm-yyyy') desc "

and the error is not detailed but it's a syntax error!

:crying:

JC

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Of course, there's a syntax error as you're following the FORMAT syntax instead of the CAST syntax.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

jcachado (1/25/2016)
This is run with a string in VS 2012, the database table is a mdb (access):
Dim SQLStringtempprd123 As String = "SELECT CAST([create_dtim],'dd-mm-yyyy'),COUNT(*) AS tot FROM Back_ickpt_prd1 WHERE CAST([create_dtim],'dd-mm-yyyy') between '01-09-2016 00:00:01' and '05-01-2016 23:59:01' GROUP BY CAST([create_dtim],'dd-mm-yyyy') order by CAST([create_dtim],'dd-mm-yyyy') desc "
and the error is not detailed but it's a syntax error!
:crying:
JC

This is the SQL Server 2012 forum section.

Try posting in the Access section. Or perhaps Luis can help with Access.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Luis Cazares (1/25/2016)
Of course, there's a syntax error as you're following the FORMAT syntax instead of the CAST syntax.

Worth a try:

Dim SQLStringtempprd123 As String = "SELECT CAST([create_dtim] AS DATE), COUNT(*) AS tot FROM Back_ickpt_prd1 WHERE CAST([create_dtim] AS DATE) between '01-09-2016 00:00:01' and '05-01-2016 23:59:01' GROUP BY CAST([create_dtim] AS DATE) order by CAST([create_dtim] AS DATE) desc"

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

ChrisM@Work (1/25/2016)
jcachado (1/25/2016)
This is run with a string in VS 2012, the database table is a mdb (access):
Dim SQLStringtempprd123 As String = "SELECT CAST([create_dtim],'dd-mm-yyyy'),COUNT(*) AS tot FROM Back_ickpt_prd1 WHERE CAST([create_dtim],'dd-mm-yyyy') between '01-09-2016 00:00:01' and '05-01-2016 23:59:01' GROUP BY CAST([create_dtim],'dd-mm-yyyy') order by CAST([create_dtim],'dd-mm-yyyy') desc "
and the error is not detailed but it's a syntax error!
:crying:
JC
This is the SQL Server 2012 forum section.
Try posting in the Access section. Or perhaps Luis can help with Access.

I only use Access... cards to enter buildings. :hehe:

A quick Google search suggest to use one of these:

DateValue([create_dtim])

Cdate(int([create_dtim]))

That's what happens when you use a forum for SQL Server to ask questions about other databases.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jcachado Mr or Mrs. 500 Points: 574 More actions · Answer 6

I appreciate your help but still not running.

:crying:

JC

jcachado Mr or Mrs. 500 Points: 574 More actions · Answer 7

Hi again,

I fix this with this.

CDate(Format(Campo,"dd-mm-yyyy"))

Thks a lot for your help,

Regards,

JC