CODEWITHSUNDEEP
sql-server-2008datetimetimezonedatetime-format
Vivek Desai
Vivek Desai

Reputation: 21

how to convert date field to specific timezone?

I am facing problem in converting date from US time to Indian Standard Time(IST) because my server timezone is as per US time zone and India is 12:30 hour ahead from us ..

ex. in US today 20-03-2015 4:05:17 And in India 2015-03-20 16:35:17

My Database Field is dateOfBirth

select DateOfBirth from student

Output:

1992-09-18
1992-03-20

but when i am adding +12:30 hour to my field it is working

select switchoffset(CAST(DateOfBirth as datetimeoffset),'+12:30') from Student

OutPut

1992-09-18 08:00:00.0000000 +08:00
2015-03-20 08:00:00.0000000 +08:00

i wrote this query for display birthdate of those persons who are having birthday today.

select dateofbirth from student where (dateofbirth = (switchoffset(CAST(GETDATE() as datetimeoffset),'+12:30')))

but comparison with today's date is not working. anyone have solution ..? please help me

thank you..

Upvotes: 2

Views: 141

Answers (2)

sanmis
sanmis

Reputation: 525

You've this in your query: dateofbirth = (switchoffset(CAST(GETDATE() as datetimeoffset) which makes sql to look for exact match of date of birth i.e. if switchoffset returns 24-03-2015 05:55:06 +12.30 (hypothetical), then dob needs be equal to this, which is impossible. try to put this in a range, something like this:

DECLARE @resultAfterUsingSwitch AS DATETIMEOFFSET;
SELECT @resultAfterUsingSwitch = switchoffset(CAST(GETDATE() as   datetimeoffset),'+12:30');
DECLARE @range1 DATETIME, @range2 DATETIME;
SELECT @range1 = CONVERT(Date, @resultAfterUsingSwitch) 
SELECT @range2 = DATEADD(dd, 1, @range1)
select dateofbirth from student where dateofbirth>=@range1 AND dateofbirth<@range2

Upvotes: 0

Eric Siodmak
Eric Siodmak

Reputation: 151

The year of student birthdat not be selected just day and month

this code return a VARCHAR MMDD

declare @monthDay VARCHAR(4) =  RIGHT('0'+cast(month(switchoffset(CAST(GETDATE() as datetimeoffset),'+12:30')) as VARCHAR(2)) ,2) +  RIGHT('0'+cast(DAY(switchoffset(CAST(GETDATE() as datetimeoffset),'+12:30')) as VARCHAR(2)) ,2)

Upvotes: 1

Related Questions