Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-17-2019, 06:33 PM   #1
adnanhome
Newly Registered User
 
Join Date: Nov 2008
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
adnanhome is on a distinguished road
date between

Hi I am trying to get :
if today is Monday then it will show result from Friday to Sunday
Else
one day before . means if today is Tue then I will show only Monday data.

--------------------------------------------------------------------
select * from POTABLE where PODATE BETWEEN

CASE
WHEN
DATENAME(WEEKDAY, GETDATE())='Monday'
THEN
CONVERT(CHAR(8), GETDATE(), 112)-3 AND CONVERT(CHAR(8), GETDATE(), 112)
ELSE
CONVERT(CHAR(8), GETDATE(), 112)-1 AND CONVERT(CHAR(8), GETDATE(), 112)
END

error
Incorrect syntax near the keyword 'AND'

please note my PODATE is 'yyyymmdd' format. where I am making mistake your help is appreciated. Thanks


Last edited by adnanhome; 11-17-2019 at 06:39 PM.
adnanhome is offline   Reply With Quote
Old 11-17-2019, 07:24 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,570
Thanks: 58
Thanked 1,437 Times in 1,418 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: date between

Hi. Maybe you meant to use the + sign instead of AND?

Edit: That's probably not it. How about using only one AND after the END?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-17-2019, 07:37 PM   #3
adnanhome
Newly Registered User
 
Join Date: Nov 2008
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
adnanhome is on a distinguished road
Re: date between

I figure out myself. is this ok ?

select * from POTABLE where PODATE BETWEEN

CASE
WHEN
DATENAME(WEEKDAY, GETDATE())='Monday'
THEN
CONVERT(CHAR(8), GETDATE(), 112)-3
ELSE
CONVERT(CHAR(8), GETDATE(), 112)-1
END

AND CONVERT(CHAR(8), GETDATE(), 112)

adnanhome is offline   Reply With Quote
Old 11-17-2019, 07:54 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,570
Thanks: 58
Thanked 1,437 Times in 1,418 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: date between

Hi. Glad to hear you got it sorted out. As long as it works. Cheers!

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
adnanhome (11-18-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Generate a Due date with a start date from 1 table and a completion date from another jjake Forms 9 08-15-2016 05:24 AM
[SOLVED] Date Field to AutoPopulate other date fields to futrue date demicay2669 Modules & VBA 4 10-29-2013 09:32 AM
Query criteria Date() not accurate for fields set as General Date (date/time) isaacski Queries 3 05-24-2013 06:51 PM
Date command returns wrong date despite system date being correct michal79 Visual Basic 8 03-05-2013 06:05 AM
When is a date not a date? Seeking ultimate regional date settings solution... ML! Modules & VBA 5 10-21-2010 09:10 AM




All times are GMT -8. The time now is 12:55 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World