Formatted Search - Date Time Stamps

[Former Member]

Hi, We are trying to use a Date & Time Stamp for two udf's of which I will then calculate the difference in a report. I have a couple of issues. Ideally the user would enter the Start field (udf1), click Shift-F2 and that udf would be populated with a Date/Time Stamp like: 07/12/09 14:30:00 or 07/12/09 02:30PM. The user would also do the same thing for the End field (udf), so I end up with two udf's that both hold a Date/Time Stamp described above. 1 - It looks like I will either need two udf's each for both stamps, one for the Date (date field type) and one for the Time (hour field type)...or I will need to use an alphanumeric field type to hold the whole timestamp. When using the hour field type I still have some issues. and 2 - I can't seem to get the right sql (sbo query) function to return the date/time stamp and / or I can't cast it or format it the way I would like. -If the udf is date field, I can use the GetDate function, but it doesn't seem to get the time with it or the field isn't big enough or formatted properly. -If the udf is an alphanumeric field, I cast the GetDate function as nvarchar and it returns the date/time stamp, but it is an alpha field and the date is Jul 21 2009 4:41PM. I don't like the Jul 21 2009 formatting -If I use two fields, one for date (date type) and one for time (hours type), the date is formatted the way I like, but the hours field doesn't display the AM or PM and my formatted search doesn't return military time (14:00 instead of 2:00PM), so I can't tell if 2:00 is morning or afternoon. So I guess my questions are pretty general...Am I using the correct field types? Any suggestions on what to use? Are there any other ways to format this data? Not all date functions or casts (time, smalldatetime) work in the sbo query so I am looking for options. Any suggestions on what functions or cast to use? Thanks

[Former Member · Accepted solution]

Hi,

As you already aware, within B1 you may not treat datetime field easily. For one of your request: I don't like the Jul 21 2009 formatting. You could use function such as

Select CONVERT(nvarchar(30), GETDATE(),106)

to change it to 21 Jul 2009.

Thanks,

Gordon