Getting "00:00:00" time value into Date Time for a week ending date

Database
Highlighted

Getting "00:00:00" time value into Date Time for a week ending date

Hello - new to Teradata SQL and new user in this community:

 

I am trying to get to a MM/DD/YYYY 00:00:00 format.  Here is the scenario:

 

I have a Teradata view that produces results sorted by week ending (Fridays) that I need to join with a MS Access table that sorts its data also by week ending.  So, my left join will be keyed on the week ending date from each data source.  My challenge is the MS Access date field is stored as date / time.  In my MS Access source data, I have the correct MM/DD/YYYY dates and each has "00:00:00" as its time value.  So, in order for me to properly join the two tables, I need to get my Teradata view to produce a timestamp value where the date is the correct Friday (I have this result now) and the time portion should be set to all 0's, that is "00:00:00".

 

Thanks for any direction and help you can provide.


Accepted Solutions
Senior Apprentice

Re: Getting "00:00:00" time value into Date Time for a week ending date

"next Friday (including today)":

Next_Day(datecol -1, 'fri')

 

If it's a datetime in Access you probably don't need to care about formatting, simply calculate the matching date and then cast it as a timestamp (which will result in 00:00:00):

Cast(Next_Day(datecol -1, 'fri') AS TIMESTAMP(0)) 

 

To get a formatted string use:

To_Char(Next_Day(datecol -1, 'fri'), 'MM/DD/YYYY HH:MI:SS')
1 ACCEPTED SOLUTION
2 REPLIES
Senior Apprentice

Re: Getting "00:00:00" time value into Date Time for a week ending date

"next Friday (including today)":

Next_Day(datecol -1, 'fri')

 

If it's a datetime in Access you probably don't need to care about formatting, simply calculate the matching date and then cast it as a timestamp (which will result in 00:00:00):

Cast(Next_Day(datecol -1, 'fri') AS TIMESTAMP(0)) 

 

To get a formatted string use:

To_Char(Next_Day(datecol -1, 'fri'), 'MM/DD/YYYY HH:MI:SS')

Re: Getting "00:00:00" time value into Date Time for a week ending date

Thank you!  I got it to work and results in the data I was trying to get.