June 13, 2017 at 9:23 am
Hello, I have taken over another colleagues sql project and am not very versed in syntax as of yet, but am needing to convert the 'real_date' to a format of MM/DD/YY whereas currently it is MMDDYY. I believe its because he had it set as a varchar, but on what is listed below how can I change this?
thanks(
SELECT
DISTINCT drs.*, amount, drs.payee_name,
bank_account.account_number,
(substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4)) as check_date,
(substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4)) as void_date,
CAST(
CASE
WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
THEN 'CN'
ELSE 'IS'
END AS VARCHAR) as status,
CAST(
CASE
WHEN CAST(void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
THEN (substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4))
ELSE (substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4))
END AS VARCHAR) AS real_date
FROM drs_check as drs
INNER JOIN payee ON payee.id = drs.payee_id
INNER JOIN bank_account ON bank_account.account_number = '501112215'
WHERE
(drs.company_id = 'TMS2')
AND
(drs.check_number NOT LIKE 'd%' AND drs.check_number NOT LIKE '%a%')
AND
(CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE) OR drs.check_date = CAST(dateadd(day, -1, getdate()) AS DATE))
)
UNION ALL
(
SELECT
DISTINCT drs.*, amount, drs.payee_name,
bank_account.account_number,
(substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4)) as check_date,
(substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4)) as void_date,
CAST(
CASE
WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
THEN 'CN'
ELSE 'IS'
END AS VARCHAR) as status,
CAST(
CASE
WHEN CAST(void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
THEN (substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4))
ELSE (substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4))
END AS VARCHAR) AS real_date
FROM ap_check as drs
INNER JOIN vendor ON vendor.id = drs.payee_id
INNER JOIN bank_account ON bank_account.account_number = '501112215'
WHERE
(drs.company_id = 'TMS2')
AND
(drs.check_number NOT LIKE 'd%' AND drs.check_number NOT LIKE '%a%')
AND
(CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE) OR drs.check_date = CAST(dateadd(day, -1, getdate()) AS DATE))
)
June 13, 2017 at 9:32 am
You can use the convert function instead of the cast function and within the function use the style to determined how the date should look like. Because the date is stored as varchar (by the way dates should be store as dates and not as string, but this is a different story), you'll need to convert it to date and then convert it again to string with the desired format. Here is a small example:
declare @ch char(8)
set @ch = '06/13/17'
--Converting the string to datetime
select convert(datetime,@ch,1)
--Converting the "datetime" to string with the specified style
select convert(char(8),convert(datetime,@ch,1),3)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 13, 2017 at 9:42 am
could you tell me where in my script this would need to be done?
June 13, 2017 at 9:45 am
Anyplace you need the format returned, use CAST/CONVERT. It's not needed for the joins, just the return values.
June 13, 2017 at 10:00 am
so I think its just the two parts which end AS real_date, so from below
WHEN CAST(void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
THEN (substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4))
ELSE (substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4))
END AS VARCHAR) AS real_date
how would I write this? I'm sorry I'm really not versed at sql and don't see where I need to put this to display the date as MM/DD/YY
thanks for any help provided
June 13, 2017 at 10:31 am
You should really provide DDL to get a good answer.
1. I strongly suspect your dates are acutally datetimes and a poor effort has been made to do comparisons.
Casting is a bad idea as it is expensive and is not SARGABLE. If you do cast, always specify the length of the string. ie varchar(8) not varchar.
2. You should really let the front end do the date formatting.
Here is your first SELECT with the formatting:
SELECT DISTINCT drs.*, amount, drs.payee_name, bank_account.account_number
,CONVERT(char(8), drs.check_date, 1) AS check_date
,CONVERT(char(8), drs.void_date, 1) AS void_date
,CASE
WHEN drs.void_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -1)
AND drs.void_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
THEN 'CN'
ELSE 'IS'
END AS [status]
,CASE
WHEN drs.void_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -1)
AND drs.void_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
THEN CONVERT(char(8), drs.void_date, 1)
ELSE CONVERT(char(8), drs.check_date, 1)
END AS real_date
FROM drs_check as drs
INNER JOIN payee ON payee.id = drs.payee_id
INNER JOIN bank_account ON bank_account.account_number = '501112215'
WHERE drs.company_id = 'TMS2'
AND drs.check_number NOT LIKE 'd%'
AND drs.check_number NOT LIKE '%a%'
AND
(
(
drs.void_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -1)
AND drs.void_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
)
OR
(
drs.check_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -1)
AND drs.check_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
)
);
June 13, 2017 at 10:41 am
rmcguire 28206 - Tuesday, June 13, 2017 10:00 AMso I think its just the two parts which end AS real_date, so from below
WHEN CAST(void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
THEN (substring(cast(cast(drs.void_date as date) as varchar), 6, 2)+substring(cast(cast(drs.void_date as date) as varchar), 9, 2)+substring(cast(cast(drs.void_date as date) as varchar), 1, 4))
ELSE (substring(cast(cast(drs.check_date as date) as varchar), 6, 2)+substring(cast(cast(drs.check_date as date) as varchar), 9, 2)+substring(cast(cast(drs.check_date as date) as varchar), 1, 4))
END AS VARCHAR) AS real_datehow would I write this? I'm sorry I'm really not versed at sql and don't see where I need to put this to display the date as MM/DD/YY
thanks for any help provided
Your formula is very simple. It's converting the column to date data type using CAST, then to varchar in a YYYY-MM-DD format (default for date data type), then taking the different parts to set them in the desired order.
All that work can be reduced by using CONVERT which allows format codes. The format code 101 will return any date/time date type in the format MM/DD/YYYY.
Check the documentation to know how to make it work. https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
June 13, 2017 at 11:55 am
can I have someone explain how on my original script this:
WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)
differs from this
WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -3, getdate()) AS DATE)
as far as that dates of the data getting pulled on what would need to be changed on SSCrazy's example above to accomodate for this?
thanks again for helping me understand this
June 13, 2017 at 12:10 pm
rmcguire 28206 - Tuesday, June 13, 2017 11:55 AMcan I have someone explain how on my original script this:
WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -1, getdate()) AS DATE)differs from this
WHEN CAST(drs.void_date AS DATE) = CAST(dateadd(day, -3, getdate()) AS DATE)as far as that dates of the data getting pulled on what would need to be changed on SSCrazy's example above to accomodate for this?
thanks again for helping me understand this
Run the following:SELECT CAST(dateadd(day, -1, getdate()) AS DATE), CAST(dateadd(day, -3, getdate()) AS DATE)
The difference should be obvious.
June 13, 2017 at 2:14 pm
so for.....
drs.void_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -3)
AND drs.void_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
will return the records with void _date between 3 days before the current date and the current date, correct?
June 13, 2017 at 7:41 pm
rmcguire 28206 - Tuesday, June 13, 2017 2:14 PMso for.....
drs.void_date >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -3)
AND drs.void_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)will return the records with void _date between 3 days before the current date and the current date, correct?
Yes, but no including the current date.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply