date and time

[Ishwarkumar]

(WHERE t1.gdt_login_date >=today().-30;) i have used this date function. but i'm getting time with date. i want only date with out time.

[Kurt_Bremser]

To extract the data from a datetime value, use the datepart() function.

[Ishwarkumar]

ya. but i'm not getting it.

PROC SQL;

CREATE TABLE WORK.FILTER_FOR_GBLT_USER_LOG AS

SELECT t1.gnum_userid,

t1.gnum_seat_id,

t1.gdt_login_date,

t1.gdt_logutt_date,

t1.gstr_ip_number,

t1.gnum_hospital_code,

t1.gnum_user_type_id

FROM ANDHRA.GBLT_USER_LOG t1

WHERE t1.gdt_login_date >=today()-30;

QUIT;

this is the program i have used.

can you show mw the syntax by using today function

[RW9]

So what part do you not get?  Your code seems fine, though you would go a long way if you apply some good coding practices to it to make it readable (and use the {i} to post code):

proc sql;
  create table WORK.FILTER_FOR_GBLT_USER_LOG as
  select  T1.GNUM_USERID,
          T1.GNUM_SEAT_ID,
          T1.GDT_LOGIN_DATE,
          T1.GDT_LOGUTT_DATE,
          T1.GSTR_IP_NUMBER,
          T1.GNUM_HOSPITAL_CODE,
          T1.GNUM_USER_TYPE_ID
  from    ANDHRA.GBLT_USER_LOG T1
  where   T1.GDT_LOGIN_DATE >= (today()-30);
quit;

The only thing I added was the brackets around the today() - 30 to make it clear we want 30 days off todays date.  Do be aware this assumes that T1.GDT_LOGIN_DATE is a date variable.  If its a datetime then:

  where   datepart(T1.GDT_LOGIN_DATE) >= (today()-30);

[Kurt_Bremser]

From the code you sent me in a personal message:

PROC SQL;
  CREATE TABLE WORK.FILTER_FOR_GBLT_USER_LOG AS
  SELECT t1.gnum_userid,
    t1.gnum_seat_id,
    DATEPART(t1.gdt_login_date) format=date9. as  gdt_login_date,
    DATEPART(t1.gdt_logutt_date) format=date9. as  gdt_logutt_date,
    t1.gstr_ip_number,
    t1.gnum_hospital_code,
    t1.gnum_user_type_id
  FROM ANDHRA.GBLT_USER_LOG t1
  WHERE calculated gdt_login_date >=today()-30;
QUIT;

t1.gdt_login_date is still a datetime value and will always be a much larger than today() - 30.

(seconds from 01jan1960:00:00:00 vs days from 01jan1960)

[Ishwarkumar]

I'm having a data. when i'm running it. its showing blank in the result.

PROC SQL;

CREATE TABLE WORK.FILTER_FOR_GBLT_USER_LOG_0005 AS

SELECT t1.GNUM_USERID,

t1.GNUM_SEAT_ID,

t1.GDT_LOGIN_DATE,

t1.GDT_LOGUTT_DATE,

t1.GSTR_IP_NUMBER,

t1.GNUM_HOSPITAL_CODE

FROM ORARAJ.GBLT_USER_LOG t1

WHERE t1.GDT_LOGIN_DATE between '07Mar2017:00:00:00'dt and '06Feb2017:00:00:00'dt;

QUIT;

　

this is the code i have used... can u solve this thing...

[Ishwarkumar]

And it is not even showing errors.

[ballardw]

---

@Ishwarkumar wrote:

And it is not even showing errors.

---

And you haven't shown any actual values for the t1.gdt_login_date variable.