May 22, 2003 at 7:16 am
Can anyone help to convert 2003-04-09 00:00:00.000 to this format :8-Apr-03
Thanks in advance.
May 22, 2003 at 7:51 am
This does what you ask....
DECLARE @myDate datetime
SET @myDate = CONVERT(datetime,'2003-04-09 00:00:00.000')
print convert(varchar,DAY(@myDate)) + '-' + substring(datename(mm,@myDate),1,3) + '-' + substring(convert(varchar,YEAR(@mydate)),3,2)
Is that what you wanted?
RD Francis
R David Francis
May 22, 2003 at 7:55 am
I'm assuming that you wanted 9-Apr-03, not 8-Apr-03. If you really wanted yesterday's date, then use DATEADD to add -1 days to the date before you start to work with it.
If you want to treat the day information as if the date does not change when you go from 11:59:59 to 12:00:00 but when you go from 12:00:00.000 to 12:00:00.001, that's a different question: Try:
SET @myDate = DATEADD(ms,-1,CONVERT(datetime,'2003-04-09 00:00:00.000'))
in my previous code to get that effect.
RD Francis
R David Francis
May 22, 2003 at 8:03 am
OK, forgot to try that before I posted:
You actually have to add -2 or -3 to get the date to flip back to yesterday, at least on my machine. It appears that the time format resolution is around three milliseconds.
Couldn't locate anything in SQL BOL to confirm, but I didn't devote hours of my life to it either.
RD Francis
R David Francis
May 22, 2003 at 8:10 am
That works as i required. How do i implement it to within sql query. Say i have table abc with columns t1,t2 both are date fields with format :2003-04-09 00:00:00.000 . how do i get your query for retriving the records.
May 22, 2003 at 8:28 am
Thats the best I can do.
SELECT REPLACE(CONVERT(varchar(12), CAST('2003-04-09 00:00:00.000' AS DATETIME), 6),' ', '-')
(and it will be 09-Apr-03 not 08-Apr-03)
Hope this helps.
Regards.
May 22, 2003 at 8:40 am
Not sure exactly what you're going for here.
The following query should select only values where t1 is midnight, April 9, 2003, and would return t1 and t2 in the format requested:
select convert(varchar,DAY(t1)) + '-' + substring(datename(mm,t1),1,3) + '-' + substring(convert(varchar,YEAR(t1)),3,2) as Time1
,convert(varchar,DAY(t2)) + '-' + substring(datename(mm,t2),1,3) + '-' + substring(convert(varchar,YEAR(t2)),3,2) as Time2
where t1 = '9-Apr-03'
Note that providing just a date with no time information to a datetime field gives you that date at midnight.
RD Francis
R David Francis
May 26, 2003 at 3:03 am
Hi,
Try this... convert(varchar,cast(thisdate as datetime),6)
thisdate is the date u want to convert.
example: select convert(varchar,cast('2003-04-09 00:00:00.000' as datetime),6) will print
09 Apr 03
Hope this will solve ur problem,
Happy Programming
Gopal
May 29, 2003 at 6:17 am
Since you have two issues here, first split them up. As mentioned, use DATEADD to add or subtract days to get the desired date.
DECLARE @myDate varchar(20), @dtDate datetime
set @dtDate = '2003-04-09 00:00:00.000'
-- backup one day
SELECT @dtDate = DATEADD(day, -1, @dtDate)
-- package date into char
select @myDate = DATENAME(d,@dtdate) + '-'
+ LEFT(CONVERT(VARCHAR(12),@dtDate,7),3) + '-'
+ RIGHT(CONVERT(VARCHAR(12),@dtDate,7),2)
-- check
PRINT@myDate
What's the business problem you're trying to solve?
May 30, 2003 at 5:41 am
select convert(varchar(20), getdate(), 6)
will give u converted format ...
select convert(varchar(20), getdate()-1, 6)
will give u converted format with i day less as per ur reqmt...
happy programming
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply