Getdate() function to get date for my timezone
Since Sql Server 2016 you can use AT TIME ZONE
...
SELECT CONVERT(datetime2(0), '2015-03-29T01:01:00', 126)
AT TIME ZONE 'Central European Standard Time';
... as specified in the documentation
You can use SYSDATETIMEOFFSET
function
select SYSDATETIMEOFFSET()
MSDN description:
Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.
More on MSDN.
Based on clarification in the comment below:
Because you want to store the local time of the client, SQL Server has no way of knowing what is your local time. The best option that would work best would be to send the current time from the client each time.
SELECT GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'
You need the first 'AT TIME ZONE UTC'
in order to tell the DB what the value currently is in, so it knows how to get to the second given time zone, 'Central Standard Time'
in my example.