DB has a date field and it looks like it defaults to 1753-01-01 00:00:00.000 and when a date is entered it looks like this, 2023-03-28 00:00:00.000. I am trying to pull any records that do not equal the default value, in Access where my old DB was I just had to do a "Is Not Null" statement. I am guessing SQL is different?
It is possible to define a default other than NULL. It looks like you can select non-default values by:
WHERE YourDate > '1753'When I try that statement it says "Your entry cannot be converted to a valid date time value." I am trying to look at another view someone helped me with but I think it is just "getting" a date, not sure how it formats it?
'CAST(GETDATE() AS date)' I don't think this would work for this instance.
WHERE date_column >= '19500101' /* or whatever other starting date you want to use *./
So I tried this and the SQL DB freezes when I tab off of the filter field when I enter this format. This happened to me yesterday as well when I tried a different format, I have to close the DB and restart it. The source data is from SAGE 100 not sure if that matters? This is what the data looks like in SAGE, I know in some script I have to format the date with code similar to this vbs.
rVal = oSession.FormatDate(oSession.SystemDate, dToday, "%M/%D/%Y")
dToday = oSession.GetFormattedDate(CStr(dToday))
Here is a picture of the data within SAGE.
I got the SQL to run but it is including the null values, what is wrong? i also tried a <= indicator.
Blockquote
SELECT dbo.SO_SalesOrderHeader.SalesOrderNo, dbo.SO_SalesOrderHeader.BillToName, dbo.SO_SalesOrderHeader.OrderDate, dbo.SO_SalesOrderHeader.UDF_NEWHIRE, dbo.SO_SalesOrderHeader.UDF_EDIT,
dbo.SO_SalesOrderHeader.UDF_NEWDESIGN, dbo.SO_SalesOrderHeader.UDF_OLDDESIGN, dbo.SO_SalesOrderHeader.UDF_SHIPBYDATE, dbo.SO_SalesOrderHeader.UDF_NEEDDATE,
dbo.SO_SalesOrderHeader.UDF_EVENTDATE, dbo.SO_SalesOrderHeader.UDF_DATE_PRODUCTION_RECEIVED, dbo.SO_SalesOrderHeader.UDF_DATE_PRODUCTION_COMPLETED, dbo.SO_SalesOrderDetail.ItemCode,
dbo.SO_SalesOrderDetail.QuantityOrdered, dbo.SO_SalesOrderHeader.OrderType, dbo.SO_SalesOrderHeader.UDF_MONOGRAMING
FROM dbo.SO_SalesOrderHeader INNER JOIN
dbo.SO_SalesOrderDetail ON dbo.SO_SalesOrderHeader.SalesOrderNo = dbo.SO_SalesOrderDetail.SalesOrderNo
WHERE (dbo.SO_SalesOrderHeader.UDF_MONOGRAMING = 'Y') AND (dbo.SO_SalesOrderHeader.OrderType = 'S') AND (dbo.SO_SalesOrderHeader.UDF_DATE_PRODUCTION_RECEIVED <> CONVERT(DATETIME, '1753-01-01 00:00:00',
102)) OR
(dbo.SO_SalesOrderHeader.OrderType = 'B')
Blockquote
If this is true:
OR
(dbo.SO_SalesOrderHeader.OrderType = 'B')
then the row will be selected regardless of the other conditions, because of the "OR".
In SQL, to check if a date field is not equal to a specific value, you can use the comparison operator <> or != (not equal to). However, since your default date value is '1753-01-01 00:00:00.000' , you cannot directly compare it to NULL as you would in Access.