How to format date in a SQL statement?

[OliverR]

Hey guys,

I'm using a SQL statement to insert the date into a session field, but I can't seem to find how to format it to "dd/MM/yyyy" format (or any other format, for that matter).

Here's what I've got that's working so far:

INSERT INTO Session1 SELECT GETDATE()

That's simple enough. However, it doesn't give me the date in the desired format. I've tried

SELECT CONVERT(VARCHAR, GETDATE(), 103)

because that works for some SQL Server versions. I've also tried

SELECT FORMAT (GETDATE(), 'dd/MM/yyyy')

as that seems to work for SQL Server 2012 and onwards. But neither of those work for TracerPlus, probably because TracerPlus doesn't use SQL Server

So how do I go about formatting the date? I would also need to do the same for the time, in a seperate field.

Thanks for any tips!

 

[Howard Heckman III]

Hi Oliver,

Is there a reason you need to do this through a SQL call, or can you just use a DateTime field with the appropriate formatting specified in TracerPlus Desktop? You should be able to specify the formats you require using the Format button that appears in the General Field Settings after changing the field's data type to DateTime. You can do this for both a Date field, and an additional Time field.

Let us know if this does not work for some reason.

 

[OliverR]

Hi Howie,

Thanks for the reply. The reason I was doing it with SQL is because I'm adding multiple records from Session B - along with field data from Session A - into Session A, based on 1 field in Session A. I'm using SQL to retrieve those records.

INSERT INTO Session6 (Field1, Field2, Field3, Field4, Field9, Field10, Field12) 
SELECT '[*0*]', '[*1*]', '[*2*]', '[*3*]', Field3, Field4, Field6 
FROM Session4 
WHERE Field1 = '[*8*]'

But you're right, I can just configure a DateTime field in Session A, and reference that in the SQL query. I had figured that out already, but forgot to update this topic It's working fine now.