Problem with query that has a date conversion to 'yyyy-mm-dd' format

Question

Problem with query that has a date conversion to 'yyyy-mm-dd' format

DaveBriCam

SSCarpal Tunnel

Points: 4294
More actions
August 12, 2021 at 4:29 pm

#3919321
I have a table with the field 'datestamp' stored in 'yyyy-mm-dd' format and this query is not working... any ideas?
```
SELECT COUNT (*) FROM .[dbo].[Stage_Log] with (NOLOCK) WHERE datestamp = CONVERT(varchar(10), GetDate(),20)
```

Viewing 6 posts - 1 through 5 (of 5 total)

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

ScottPletcher SSC Guru Points: 100946 More actions · Answer 1

You need format 120 rather than 20: 20 will leave off the century, so you'd get 21 for yy instead of 2021, therefore it won't match.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 2

The query below works fine now isolated, but it is part of an email stored procedure and I'm getting the error from SQL Agent:

08/12/2021 13:11:45,tel_SMS,Error,2,GSO-SSIS-DEV,Ytel_SMS,Send success email,,Executed as user: NT SERVICE\SQLSERVERAGENT. Conversion failed when converting the nvarchar value '

Today's Tel SMS Upload was Successful

<table border="1"><th>Count of Records Successfully Uploaded Today: </th></tr>' to data type int. [SQLSTATE 22018] (Error 245). The step failed.,00:00:00,16,245,,,,0

BEGIN

SET NOCOUNT ON;

DECLARE @tableHTML  NVARCHAR(MAX) ;

-- Below defines a rectangle to hold the query results
SET @tableHTML =
    N'Today''s SMS Upload was Successful
' +
    N'<table border="1">' +
    N'<th>Count of Records Successfully Uploaded Today: </th></tr>' + 

-- Below is the query of which the results are passed into the rectangle
    (SELECT COUNT(*) FROM .[dbo].[Stage_SMSLog] 
with (NOLOCK) WHERE datestamp = CONVERT(nvarchar(10), GetDate(),120)) + N'</table>';



-- Below are the specifics of sending the Email
EXEC msdb.dbo.sp_send_dbmail 
    @recipients='bcamp@company.com;',
@copy_recipients='bcamp@company.com;',
@importance='high',
    @subject = 'Today''s SMS Upload was Successful',
    @body = @tableHTML,
    @body_format = 'HTML',
    @profile_name = 'SQLMail';
SELECT CAST( GETDATE() AS Date )
END

This reply was modified 2 years, 8 months ago by DaveBriCam.
This reply was modified 2 years, 8 months ago by DaveBriCam.

ScottPletcher SSC Guru Points: 100946 More actions · Answer 3

...

N'<th>Count of Records Successfully Uploaded Today: </th></tr>' +

-- Below is the query of which the results are passed into the rectangle

CAST((SELECT COUNT(*) FROM .[dbo].[Stage_SMSLog]

with (NOLOCK) WHERE datestamp = CONVERT(nvarchar(10), GetDate(),120)) AS nvarchar(10)) + N'</table>';

...

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 4

Finally got it! this works:

(SELECT Convert (varchar, (SELECT COUNT (*) FROM .[dbo].[Stage_Log] WHERE datestamp = LEFT(CONVERT(varchar, GetDate(), 120), 10))))

ratbak Ten Centuries Points: 1330 More actions · Answer 5

You need to wrap

(SELECT COUNT(*) FROM .[dbo].[Stage_SMSLog] 
with (NOLOCK) WHERE datestamp = CONVERT(nvarchar(10), GetDate(),120))

in CAST or CONVERT when concatenating into nvarchar (or varchar) string using "+"

(CONCAT function available in 2016+ handles such conversions... and nulls... automatically)

e.g.

CAST((SELECT COUNT(*) FROM .[dbo].[Stage_SMSLog] 
with (NOLOCK) WHERE datestamp = CONVERT(nvarchar(10), GetDate(),120)) AS nvarchar(12))