Convert date within Proc SQL

Reply
New Contributor
Posts: 4

Convert date within Proc SQL

Hello,

This likely a simple question...

I have dates within a database formatted mm/dd/yyyy, and I would like to convert the date to the first of every month within a proc sql pull, retaining the date format. 

For example, I want 10/8/2013 to show up as 10/1/2013.

Any ideas?

Thanks!

Tyler

Super User
Posts: 10,538

Re: Convert date within Proc SQL

The format won't change unless you explicitly change it, so no worry there.

select mdy(month(datevariable),1,Year(datevariable)) as DateVariable

should do it.

Super Contributor
Posts: 307

Re: Convert date within Proc SQL

Are you using SQL pass-through or libname access? What type of database are you pulling the dates from? Are the variables SAS dates?

New Contributor
Posts: 4

Re: Convert date within Proc SQL

ballardw - Unfortunately, that method didn't work - mdy is not a function in Teradata sql apparently.  Sorry for not making that clear in the original post.

Fugue - I am using SQL through Teradata

Thanks for the help!

Super Contributor
Posts: 307

Re: Convert date within Proc SQL

Are you using pass-through or libname access? Functions used in the proc sql "pull" may depend on whether the query is pass-through vs libname.

Is the variable one of the supported Teradata data/time types identified at: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001384390.htm?? Or, is the date a character string?

Respected Advisor
Posts: 3,901

Re: Convert date within Proc SQL

In SAS you would use intnx() for shifting date, time or datetime values. Unfortunately intnx() is not one of the functions which gets pushed to Teradata SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition

You will need explicit SQL pass-through and use Teradata SQL functions for this.

"Googling" using the following keywords "teradata how to shift date to beginning of month" the following came up:

SQL for the current month begin date and end date | Teradata Developer Exchange

Valued Guide
Posts: 3,208

Re: Convert date within Proc SQL

There is something to know about teradata dates. .....  It is the same approach as SAS is using.

Search on the internal representation at Teradata (14.0)

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/titl...http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/titl...

The teradata SQL specfics on their forums: Date and Time Manipulation on Teradata

When installed in a good practice approach formats of SAS can be handled as in database procedures.

You shoudl have them available. SAS(R) 9.3 In-Database Products: User's Guide

Using the SAS/Access interface datatypes are translated as documented by: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition  

And having the fucntions translated from SAS to teradata: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

If you are just retrieving the data .... adjsut them after you got your results in SAS

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 6 replies
  • 2435 views
  • 0 likes
  • 5 in conversation