Date Function 3

[bajo71]

Greetings,
I'm looking for a straight-forward way to express this date format: 2026-08-01 00:00:00.000 as 260801

Using the following

Right(DATEPART(yy,maturity_date),2)+DATEPART(mm,maturity_date)
adds the integers together and I'd like not to have to concatentate etc..

Thank you

 

[hwkranger]

it's b/c you're adding numbers together. you'll need to convert/cast b4 concatonation for this to happen..

try something in the format of:

cast(year(maturity_date) as char(4)) + cast(month(maturity_date) as char(2) + ...

of course remember that month can be 1 digit, so you'll have to pad 0's...

regards.

Randall Vollen
Merrill Lynch

 

[gmmastros]

Another way (using a local variable as an example)...

Declare @Temp DateTime
Set @Temp = '2026-08-01 00:00:00.000'

Select (Year(@Temp) - 2000) * 10000 + Month(@Temp) * 100 + day(@Temp)

Year - 2000, then multiply by 10000, Add month * 100, and add the day.

-George

"the screen with the little boxes in the window." - Moron

 

[AlexCuse]

You could also convert to the popular 'YYYYMMDD' format (date format 112), and then take the right 6 characters of the resulting string:

select right(
convert(varchar(8), getdate(), 112)
, 6)

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Or....

Use the less popular date format style [!]12[/!].

Select Convert(VarChar(666), GetDate(), 12)

-George

"the screen with the little boxes in the window." - Moron

 

[hwkranger]

zomg. george -- you are too funny. You deserve a pentagram for that answer..

 

[RichardOneil]

Good Morning-

I need to create a SQL statement that retrieves data from a table based on yesterday's date, formatted as YYYYMMDD:

SELECT Data1, Data2, Data2
FROM Table1
WHERE AuditDate = 'YYYYMMDD'

I started with the GetDate() function to retrieve the system date, but I am having trouble subtracting one day and getting it into the correct format. Also of note, is that I need to have the month/day retain the leading zero when it is a single digit month/day (e.g. 09 not 9). Thanks in advance for helping the newb!

 

[gmmastros]

Try this....

SELECT Data1, Data2, Data2
FROM Table1
WHERE AuditDate = convert(varchar(8), getdate()-1, 112)

This will NOT work if AuditDate has a time component. If it does, then I would suggest...

SELECT Data1, Data2, Data2
FROM Table1
WHERE AuditDate >= convert(varchar(8), getdate()-1, 112)
      And AuditDate < convert(varchar(8), getdate(), 112)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

WHERE AuditDate > dateadd(dd, datediff(dd, 0, getdate())-1, 0)
AND AuditDate < dateadd(dd, datediff(dd, 0, getdate())+0, 0)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

I made a small typo and forgot a = sign

WHERE AuditDate >[b]=[/b] dateadd(dd, datediff(dd, 0, getdate())-1, 0)
AND AuditDate < dateadd(dd, datediff(dd, 0, getdate())+0, 0)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions