Problem with SQL command - MS SQL Server

Try the way below to truncate the date by removing the time:

sselect count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, datecolumn))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

Select allOpen in new window

Hi Huamin,

modified query but still its not loging row count in txt file. I am herewith atatching output txt file here...

DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, createdon))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>H:\FileShare\Count_Final.txt', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Select allOpen in new window

Count_Final.txt

Can I know if you want to return records on CURRENT DATE? Validate the data of this as well.

I have validated only select query with current date, validation result is 0. In SSMS i can see count as 0. But I am not sure why this count is not reflecting on txt file :)

My requirement: I have to fetch count of cases created for every 30 min interval for current date. Once SQL query is ready, I am planning to put query in SQL job and schedule to run every 30 min interval from 00 AM to 11:59 PM. The txt output file help me to analyse count as per my business need.

I am expecting output report looks like below (the output file may txt or csv)
Execution DateTime: 11122018 08:30
 0
Execution DateTime: 11122018 09:00
7
Execution DateTime: 11122018 09:30
200
Execution DateTime: 11122018 10:00
 210
Execution DateTime: 11122018 10:30
212

Is there any other solution to achieve this instead of above command?

Is it true that you expect to compare the date field by using date part only (and skip time part)? If yes, you can use my way in above.

only current date part will OK for me instead of comparing both date and time part.....Can you please help me with updated query

What is returned in the query?

tried with above query, but why it's not logging 0 (row count) in txt file?

Try to remove clause "with (nolock)" in the query.

Removed with (nolock), stil it's not displaying row count 0 in output txt file.

DECLARE @Reccnt int
SELECT @Reccnt = count(*) from databaseName.dbo.TableName where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, createdon))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>H:\FileShare\Count_Final.txt', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Select allOpen in new window

its displaying row count without where condition...whats problem with where condition?

DECLARE @Reccnt int
SELECT @Reccnt = count(*) from databaseName.dbo.TableName with (nolock)

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>H:\FileShare\Count_Final.txt', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Select allOpen in new window

Try to put the where condition. Whole query is like

SELECT @Reccnt = count(*) from databaseName.dbo.TableName where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, createdon))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

Select allOpen in new window

no luck, still it's not displaying row count 0 in output file.

DECLARE @Reccnt int
SELECT @Reccnt = count(*) from databaseName.dbo.TableName where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, createdon))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>E:\FileShare\count_BO.txt'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>E:\FileShare\count_BO.txt'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>E:\FileShare\count_BO.txt', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Select allOpen in new window

Try

use YourSchemaName
go

DECLARE @Reccnt int
SELECT @Reccnt = count(*) from TableName where Notes = 'BO' and CONVERT(DATETIME, CONVERT(DATE, createdon))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>E:\FileShare\count_BO.txt'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + '>>E:\FileShare\count_BO.txt'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>E:\FileShare\count_BO.txt', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Select allOpen in new window

Okay, well for some reason it seems to be capping based on the length / structure of the actual string.
If your quantity is a single character (less than 10), it's not printing it.  If you add " rows" or something to the end, then it will start displaying.

I'll be damned if I can tell you "why", as I've not used this feature and can't find any explanation, but that should solve your problem.

DECLARE @Reccnt int
SELECT @Reccnt = count(*) from DatabaseName.dbo.TableName with (nolock) where Notes = 'BO' and createdon between cast(getdate() as date) and dateadd(ms, -2, cast(cast(dateadd(dd,1,getdate()) as date) as datetime))

DECLARE @Logcmd1 VARCHAR(500) = 'ECHO Execution DateTime: ' + FORMAT(getdate(), 'ddMMyyyy HH:mm') + '>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd1, no_output

DECLARE @Logcmd2 VARCHAR(500) = 'ECHO Count: ' + CAST(@Reccnt AS varchar(10)) + ' rows>>H:\FileShare\Count_Final.txt'
EXECUTE master..xp_cmdshell @Logcmd2, no_output

EXECUTE master..xp_cmdshell 'ECHO. >>H:\FileShare\Count_Final.txt', no_output

PRINT ISNULL('Count: ' + CAST(@Reccnt AS varchar(10)), 'Unknown COUNT')

Select allOpen in new window

Interesting. Almost the same solution was published in https://www.experts-exchange.com/questions/29128960/help-required-on-MS-SQL-query.html?anchorAnswerId=42753342#a42753342 a few hours ago.

Thean S could explain why he is ignoring both these solutions.

The problem is in ECHO command which works differently when you use single digit as a parameter. Single digit followed by space is OK, single letter is also OK.