Date and time

[Former Member]

Hi All,

SAP Stores its document date and time in separate fields and I need to combine into a proper datetime format using a simple SQL query:

Select docdate, doctime from oinv shows:

docdate doctime

2011-12-02 00:00:00.000 1630

2012-02-09 00:00:00.000 943

Should show in proper datetime format:

2011-12-02 16:30:00.000

2012-02-09 09:43:00.000

Does anyone have SQL code for this?

Regards

Aubrey

[former_member218051 · Accepted solution]

Hi,

Try This

SELECT convert(varchar,docdate,104) + ' ' + left(convert(varchar,doctime,102),2) + ' : ' + right(convert(varchar,doctime,102),2) from oinv

thanking you

Malhaar

[Former Member · Accepted solution]

Hi Malhaar

Thank you it work perfectly fine, , does it work with times less than 2x hour digits though?

Regards,

Aubrey

[former_member218051 · Accepted solution]

Hi,

don't understand.

thanks

Malhaar

[Former Member · Accepted solution]

Hi,

I want the query to be able to pull through a date that is 1:20 instead of 13:20.

Regards,

Aubrey

[Former Member · Accepted solution]

Hi

Time I meant to say.

Thanks,

Aubrey

[Former Member]

Hi Aubrey,

Try this...

SELECT Convert(varchar (50),t0.docdate,103)  + ':' + convert (varchar (50),(T0.DocTime)) FROM OINV T0

SELECT CAST(DATEPART(YYYY,t0.docdate)AS VARCHAR)+ ' - '+ CAST(DATEPART(MM, t0.docdate) AS VARCHAR)
+' - '+CAST(DATEPART(DD, t0.docdate) AS VARCHAR)
 +'  '+CAST(DATEPART(HH,T0.DocTime)AS VARCHAR)+ ' : '+ CAST(DATEPART(MM, T0.DocTime) AS VARCHAR)
+' : '+ CAST(DATEPART(SS, T0.DocTime) AS VARCHAR) FROM OINV T0

Thanks,

Srujal Patel

[Former Member]

Hi Aubrey,

The time field in B1 is not standard. They are stored as 4 digit numbers. You need to use formula to convert to normal time format. If you search the forum, there are some working formula can be found.

Thanks,

Gordon

[Former Member]

Hi,

Use like this LEFT(t0.[TaxDate], 12) AS 'Document Date' this for docdate

or

Convert(Date,t0.[RefDate]) AS 'Posting Date'

Thanks and Regards,

Sudhir B.

Edited by: B.sudhir on Feb 23, 2012 2:28 PM

[Former Member]

Hi,

Where do I input the select statement in this query?

Thanks

Aubrey