asked on

SQL query that convert date

Dear expert

I need help with a query please, below query didn't work, well DatetimeInsert is having a format = 2018-10-11 00:24:24.04, if I simply just do a count with DECLARE @ProcessTIme date = 2017-11-11 it returns nothing, so I need to convert it, but I don't really know how.

Please help thanks.

use Prod
DECLARE @ProcessTime CONVERT(VARCHAR(10),[ProcessedTime],120) date = 2018-10-11;
SELECT Count (*)
FROM Invoice.Progress H
WHERE H.DoElectronicInvoiceToReceiver =1 AND H.DatetimeInsert = @ProcessTime

Open in new window

Geert G

your date needs to be inside quotes
otherwise it's just a calculation

WeTi

ASKER

Like this?

SELECT Count (*)
FROM Invoice.Head H
WHERE H.DoElectronicInvoiceToReceiver =1 AND H.DatetimeInsert = '2017-10-11'

Open in new window

Doesn't work, you still need to convert it...

SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WeTi

ASKER

How can I do it inside the DECLARE? and I want to format it from 2018-10-11 00:24:24.04 to only 2018-10-11 ignore the rest.

WeTi

ASKER

Well... I could do this:
H.DatetimeInsert > '2017-10-11' AND H.DatetimeInsert < '2017-10-12' but seriously better with convert...

Geert G

did you try it ?

H.DatetimeInsert =Convert(Date, '2018-10-11', 121)

WeTi

ASKER

I did but not working, returns 0 result.

Geert G

if returns a result, then it works
otherwise you'd get a syntax error

you could also check the data for that month:
https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql

SELECT * 
FROM Invoice.Head H
WHERE H.DoElectronicInvoiceToReceiver =1 
  AND DatePart(year, DatetimeInsert) = 2017
  and  DatePart(month, DatetimeInsert) = 10

Open in new window

ste5an

You need to distinguish between a date as data (the data type) and the visual formatted output in any tool or program.

Date literals in T-SQL must be enclosed in single quotes. And can use different formats. But the problem is: Formats must be interpreted. Thus we use - good coding style - an unambiguous date format. The most common in T-SQL is 'yyyymmdd' .

DECLARE @ProcessDate DATE = '20181011';

Open in new window

Then, the data types DATETIME et al. having a time portion requires that you consider this. Cause the data type precedence leads to expansion of DATE to DATETIME:

DECLARE @GetDate DATE = GETDATE();
DECLARE @GetDateTime DATETIME = GETDATE();

SELECT IIF(@GetDate = GETDATE(), 1, 0) ,
       IIF(@GetDateTime = GETDATE(), 1, 0);

Open in new window

This means that when using a DATE as predicate on a DATETIME value, you need indeed to do a range check:

USE Prod;
GO

DECLARE @ProcessDate DATE = '20181011';

SELECT COUNT(*)
FROM   Invoice.Progress H
WHERE  H.DoElectronicInvoiceToReceiver = 1
       AND H.DatetimeInsert >= @ProcessDate 
       AND H.DatetimeInsert < DATEADD(DAY, 1, @ProcessDate);

Open in new window

WeTi

ASKER

I just need to convert a date to yyyy-MM-dd format in a query and it ignore the value behind... Ste5an you did a adddate +1 that is as same as doing: H.DatetimeInsert > '2017-10-11' AND H.DatetimeInsert < '2017-10-12'

Is this a limitation of MSSQL that it simply query the date as in format 2107-10-11 with the 22:22:22,44? What i want is: sql query ignore the 22:22:22,44 and only match the 2107-10-11.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WeTi

ASKER

I would like to thank both for the help... now I could think about which query I prefer for the output, thank you both for the effort.